MySQL SELECT to return rows with change in field value

Question

This is the table1 structure:

ID_Num | id_2 | field1
12-001 | 3    | ABCD
12-001 | 3    | ABCD
12-001 | 3    | ABCD
12-001 | 1    | ABCD
12-001 | 1    | ABCD
12-002 | 3    | ABCD
12-002 | 3    | ABCD
12-002 | 3    | ABCD
12-002 | 3    | ABCD
12-002 | 3    | ABCD
12-003 | 2    | ABCD
12-003 | 2    | ABCD
12-003 | 3    | ABCD
12-003 | 3    | ABCD
12-003 | 3    | ABCD

Now I want to select all ID_Num that has a change in field id_2
It should return 12-001 because of change from 3 to 1
and 12-003 because of change from 2 to 3

John Woo · Accepted Answer · 2013-01-17 00:58:50Z

3

SELECT  ID_NUM
FROM    tableNAME
GROUP   BY ID_NUM
HAVING  COUNT(DISTINCT id_2) > 1

SQLFiddle Demo

answered Jan 17, 2013 at 0:58

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OMG Ponies Over a year ago

@paynet: Careful about relying on MySQL's GROUP BY/hidden column functionality

eggyal Over a year ago

@OMGPonies: This query doesn't though, does it?

Gordon Linoff · Accepted Answer · 2013-01-17 01:42:38Z

0

I realize that count(distinct) requires more processing than the following:

select id_num
from tablename
group by id_num
having min(id_2) <> max(id_2)

This does not treat NULL as a separate value, but there are no NULLs in the question.

answered Jan 17, 2013 at 1:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT to return rows with change in field value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related