Sql to find timediff between two rows based on ID

Question

The subject of the question is not very explanatory, sorry for that.

Ya so the question follows: I have a database structure as below where pk is primary key, id is something which is multiple for many rows.

+------+------+---------------------+
| pk   | id   | value               |
+------+------+---------------------+
|   99 |    1 | 2013-08-06 11:10:00 |
|  100 |    1 | 2013-08-06 11:15:00 |
|  101 |    1 | 2013-08-06 11:20:00 |
|  102 |    1 | 2013-08-06 11:25:00 |
|  103 |    2 | 2013-08-06 15:10:00 |
|  104 |    2 | 2013-08-06 15:15:00 |
|  105 |    2 | 2013-08-06 15:20:00 |
+------+------+---------------------+

What is really need to get is, value difference between first two rows (which is ordered by value) for each group (where group is by id). So according to above structure I need timediff(value100, value99) [ which is for id 1 group] and timediff(value104, value103) [ which is for id 2 group]

i.e. value difference of time ordered by value for 1st two rows in each group.

One way i can think to do is by 3 self joins (or 3 sub queries) so as to find the first two in 2 of them , and third query subtracting it. Any suggestions?

Answer 1

try this.. CTE is pretty powerfull!

WITH CTE AS (
  SELECT 
  value, pk, id,
    rnk = ROW_NUMBER() OVER ( PARTITION  BY id order by id DESC)
    , rownum = ROW_NUMBER() OVER (ORDER BY id, pk)
     FROM test
)
SELECT
  curr.rnk, prev.rnk, curr.rownum, prev.rownum, curr.pk, prev.pk, curr.id, prev.id, curr.value, prev.value, curr.value - prev.value
FROM CTE curr
INNER JOIN CTE prev on curr.rownum = prev.rownum -1 and curr.id = prev.id 
and curr.rnk <=1