0

My question is similar to this SO post however the difference is that I do not have a sequential ID column.

I have a table in the following form

ID | length
0  |  5
0  |  7
0  |  10
1  |  3
1  |  8
1  |  12
2  |  1
2  |  2
2  |  4
2  |  5

and I want to get the difference between successive rows in the length column grouped by the ID. So it should give

ID | length | difference
0  |  5     | NULL
0  |  7     | 2
0  |  10    | 3
1  |  3     | NULL
1  |  8     | 5
1  |  12    | 4
2  |  1     | NULL
2  |  2     | 1
2  |  4     | 2
2  |  5     | 1

I'm not sure how to go about doing this. I tried giving each ID a separate ID that is sequential, but it turned out to be way to complicated and I could not get it to work. Can someone suggest a better way of doing it?

Community
  • 1
  • 1
user1893354
  • 5,778
  • 12
  • 46
  • 83
  • Sometimes the complexity involved in trying to get the job done with one tool (mySQL) outweighs the effort involved in using a different tool (writing some code). In this case, some PHP code would be almost trivial to write. – SaganRitual Jun 03 '14 at 18:58
  • select id, length,(select (l2.length)-l1.length from lengthtb l2 WHERE l2.length < l1.length and l2.id=l1.id order by length desc LIMIT 1 ) as 'difference' from lengthtb l1 – ITroubs Jun 03 '14 at 18:59
  • @GreatBigBore and sometimes it's very easy ;-) - although you *do* need some way of formalising the sequence, i.e. a PRIMARY KEY! – Strawberry Jun 03 '14 at 19:11
  • But does it outweigh the effort involved in learning a different tool? :) – user1893354 Jun 03 '14 at 19:12
  • @user1893354 If you do not have a PRIMARY KEY, then you don't really have a table (in an RDBMS sense), so the problem becomes rather abstract. In this instance, you do appear to have UNIQUE values, and they do appear to be arranged in ascending order, so maybe that is a candidate key. – Strawberry Jun 03 '14 at 19:16
  • having a primary key would have made it so easy http://sqlfiddle.com/#!2/029e1/3 – Abhik Chakraborty Jun 03 '14 at 19:30

2 Answers2

2

Assuming (id,length) is UNIQUE...

SELECT x.*
     , x.length - MAX(y.length) diff  
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.length < x.length 
 GROUP 
    BY x.id
     , x.length;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
1
select a.id, a.length, b.length, b.length - a.length as difference
from mytable a, mytable b
where a.id=b.id
and b.length = (select min(length) from mytable where id=a.id and length > a.length)
Alex Monthy
  • 1,827
  • 1
  • 14
  • 24
  • 3
    Please explain ***how*** your solution helps the OP. – Sunny Patel Jun 03 '14 at 19:37
  • @Sunny Patel and others: I'm really getting pissed off for getting downvoted for what in the end gets accepted as a solution. Why don't you try it before you judge it? – Alex Monthy Jun 03 '14 at 23:38
  • There's something wrong here; I just can't put my finger on it. If/in case I'm wrong, edit your post and I'll give you your point back. – Strawberry Jun 03 '14 at 23:44
  • 1
    I didn't downvote, but just posting code without explaining why your solution is in fact a solution doesn't help future readers or maybe even the OP to understand why your solution is the way to go. Explaining how you're using a subquery to accomplish the task would be an excellent start. – Sunny Patel Jun 03 '14 at 23:55