Wednesday, 7 August 2013

Sql to find timediff between two rows based on ID

Sql to find timediff between two rows based on ID

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?

No comments:

Post a Comment