DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query problem

  1. #1
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51

    Query problem

    hi all,
    I have a table in which Amount1 ,Amount2 are two attributes.
    Is there any way in SQL where I can compare Amount2 of a row
    with Amount1 of the next row.

    For eg.
    Amount1 Amount2
    100 10
    90 15
    15 18

    Now I want to Select Amount1 only if Amount2 of second row is equal to Amount1 of next row which holds for second and third rows in this case.
    regards
    nik

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    If I understood your question correctly you can look into LEAD and LAG functions.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    try this...


    create table test(Amount1 number, Amount2 number);
    insert into test values(100, 10);
    insert into test values(90, 15);
    insert into test values(15, 18);
    commit;
    select Amount1, Amount2 from test;

    select Amount1, lag(Amount2) over (order by amount2) from test;

    Cheers!
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    try this...

    I have also included the query.

    create table test(Amount1 number, Amount2 number);
    insert into test values(100, 10);
    insert into test values(90, 15);
    insert into test values(15, 18);
    insert into test values(18, 20);
    commit;
    select Amount1, Amount2 from test;

    select Amount1, lag(Amount2) over (order by amount2) from test;

    select a.Amount1, x.lag_amt from test a, (
    select Amount1, lag(Amount2) over (order by amount2) lag_amt from test) x
    where a.Amount1 = x.lag_amt;

    Cheers!
    Cheers!
    OraKid.

  5. #5
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51
    thanx balaji & Sanjay
    I think that wud solve my prob...



    Originally posted by balajiyes
    try this...


    create table test(Amount1 number, Amount2 number);
    insert into test values(100, 10);
    insert into test values(90, 15);
    insert into test values(15, 18);
    commit;
    select Amount1, Amount2 from test;

    select Amount1, lag(Amount2) over (order by amount2) from test;

    Cheers!
    regards
    nik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width