CODEWITHSUNDEEP
mysqlselecttriggers
badr bo
badr bo

Reputation: 23

select when record change value

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

Upvotes: 1

Views: 48

Answers (2)

Digital Chris
Digital Chris

Reputation: 6202

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.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 0

Related Questions