CODEWITHSUNDEEP
mysql
Chris R.
Chris R.

Reputation: 699

MySQL Group by one column if another has changed?

I've been looking to see if there's a MySQL selector that would allow me to select by one column if another has a different value.

Example:

id name  value
----------------
1  john   1
2  craig  1
3  john   2

So, what I'd be looking to do is select both rows 1 and 3 since they have the same name and the value has changed.

If this isn't possible, I can parse through all the results in code, but if MySQL can do this for me, even better!

Upvotes: 1

Views: 105

Answers (3)

Serif Emek
Serif Emek

Reputation: 674

this may work;

select * from table a
where exists 
(select b.id from table b
where a.name = b.name and a.value <> b. value)

Upvotes: 0

Jens
Jens

Reputation: 69440

Try this:

Select * from `table` where Name in (
select name from `table` Group by Name having Count(*)>1)

Inner select Looks for names that exists more than one in your table and the outer select get the data for that name.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

You could select all the names that have more than value, and then all the rows with those names:

SELECT *
FROM   mytable
WHERE  name IN (SELECT   name
                FROM     mytable
                GROUP BY name
                HAVING   COUNT(*) > 1)

Upvotes: 0

Related Questions