Results 1 to 4 of 4

Thread: qry reg LAG

  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: qry reg LAG

    how can i get 2 or more colums of the previous record using the lag function

    ie, any other equivalent for the below..... hope the below one is not efficient..not even sure wheather it works... pls suggest a way

    select a,b,lag(a,1) over (order by c,d) prev_a, lag(b,1) over (order by c,d) prev_b from ....


    the order by remains the same...
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Youve answered your own question. LAG cannot retrieve multiple columns so you just use LAG multiple times ensuring you have the same order by.

    Alan

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    ok Alan, what abt the performance...... does oracle execute the order by multiple times???????
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well I havent tried it but I suspect not. Why not try a query with a single LAG function followed by another query with two and look at the execution plans. Also look at the stats generated by putting tracing on.

    Alan

Posting Permissions

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