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

Thread: sql stmt

  1. #1
    Join Date
    Apr 2002
    Posts
    61

    Unhappy sql stmt

    I have the data like this.

    machine_id cumulative_hours
    1000 200
    1001 500
    1002 900
    ---------------------------------------------

    I want the result to be converted into non-cumulative values like this.

    machine_id hours
    1000 200
    1001 300
    1002 400

    Essentially, after first row, every next row should have values equals to (current value-previous value).

    I can do it in PL/SQL, but I prefer to do this in SQL statement for performance.

    Any ideas..

    Thanks.

  2. #2
    Join Date
    Jun 2002
    Posts
    8
    try this
    select machine_id, cumulative_hours - nvl(lag(cumulative_hours) over (order by cumulative_hours asc), 0) as hours from table_name

    hth

  3. #3
    Join Date
    Apr 2002
    Posts
    61
    Wow, this works great. Thanks a lot for your feedback. I am not aware of some of the key words that you have used (i.e, lag, over). Now, I will go back to SQL book and try to figure this out.
    Thanks again.

  4. #4
    Join Date
    Jun 2002
    Posts
    8
    LAG is a analytical function.

    just check this URL

    http://www.akadia.com/services/ora_a...functions.html

    Thx
    Aks

  5. #5
    Join Date
    Apr 2002
    Posts
    61

    Thanks Aks for the link

    Thanks Aks for the link

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