select when record change value

Question

I'm trying to select just records which changed values compared to previous record,

my table is looking like this

Id(int) | value(boolean) |
-------------------------
1       | 0              |
-------------------------
2       | 1              |
-------------------------
3       | 1              |
-------------------------
4       | 1              |
-------------------------
5       | 0              |
-------------------------
6       | 0              |
-------------------------

I must get id:2,5

thanks for your help

Go on - try something, anything. And is id always incremental — Strawberry, Mar 27 '14 at 16:39

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

I did a self-join, but instead of joining identical ids, I join t1.id = t2.id-1 and then compare the values:

select t2.id 
from thetable t1
inner join thetable t2 on t1.id = t2.id-1
where t1.value != t2.value

sqlfiddle: http://sqlfiddle.com/#!2/6d626/4

Edit to add: Thanks to @Ravinder, I figured out a way to do this even if the ids aren't sequential. I used this related question.

SET @a :=0;
SET @b :=1;
SELECT t1.id, t1.rownum
FROM
(SELECT if(@a, @a:=@a+1, @a:=1) as rownum, id, value FROM thetable) AS t1
LEFT JOIN
(SELECT if(@b, @b:=@b+1, @b:=1) as rownum, id, value FROM thetable) AS t2
ON t1.rownum = t2.rownum
where t2.value != t1.value

SQLFiddle with non-sequential ids

Basically if you don't have sequential ids you create your own. I called them rownum.

edited May 23 '17 at 10:26

Community

1
1

answered Mar 27 '14 at 16:41

Digital Chris

6,177
1
20
29

Idea is good. Let us hope id is stored sequential and no records in between are deleted or altered. – Ravinder Reddy Mar 27 '14 at 16:48
@Ravinder it would still work in this case, assuming the desired functionality was to only show changes between sequential ids: http://sqlfiddle.com/#!2/1c4c5b/1 ...but if asker wanted difference to return 9 in my example... not so much. – Digital Chris Mar 27 '14 at 16:55
@Ravinder, I came up with a solution for non-sequential case as well. – Digital Chris Mar 27 '14 at 18:03

M Khalid Junaid · Answer 2 · 2014-03-27T16:49:48.163

0

You can use mysql variable in query to check if its new or unchanged

SELECT
`Id`, 
`value`,
(CASE when @test=value then 'unchanged'
else 'changed' end) occurance,
@test:=`value`  testvar
FROM
  Table1 
,(SELECT @test:='new') t
HAVING occurance='changed'
ORDER BY `Id`

See fiddle demo

edited Mar 27 '14 at 16:49

answered Mar 27 '14 at 16:39

M Khalid Junaid

63,861
10
90
118

This should really have an ORDER BY clause. – Strawberry Mar 27 '14 at 16:46

select when record change value

2 Answers2

See fiddle demo