Results 1 to 4 of 4

Thread: Difference between rows

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Difference between rows

    I need to find the difference between columns in consecutive rows where the type is datetime. I have a query to sort the data, but can't rely on the index of each for this problem.

    I think I have a solution if I dump the sorted rows in another table where the index is in order and incremented by one for each row, but I'd like to avoid using another table. Basically, the sorted rows are obtained with

    SELECT f1, f2, datetime FROM table
    ORDER BY f1, f2, datetime;

    One catch is that for the rows where the difference is computed, f1 and f2 and to be the same between the two rows.

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can you post some sample data and result you like?

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    Quote Originally Posted by rmiao View Post
    Can you post some sample data and result you like?
    Sure.

    | 6006 | 09008501 | 2009-02-12 08:51:35 |
    | 6006 | 09008501 | 2009-02-12 09:23:44 |
    | 6006 | 09008501 | 2009-02-12 09:26:27 |
    | 6006 | 09008501 | 2009-02-12 10:16:44 |
    | 6006 | 09008601 | 2009-02-24 13:58:57 |
    | 6006 | 09008601 | 2009-02-24 14:15:15 |
    | 6006 | 09008701 | 2009-02-23 11:51:24 |
    | 6006 | 09008701 | 2009-02-23 12:01:31 |
    | 6007 | 09008701 | 2009-02-23 13:34:59 |
    | 6007 | 09008701 | 2009-02-23 14:08:15 |


    The result I'm looking for is

    6006 09008501 total1
    6006 09008601 total2
    6006 09008701 total3
    6007 09008701 total4

    where total is the total of the differences between consecutive rows of the times where the first and second fields are the same.

    I hope that makes sense.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

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