Last Item In MySQL COUNT(*) Result?

Question

Hello!

Quick question... I have a query that checks for duplicates that looks like this:

 SELECT COUNT( * ) AS repetitions, Name, Phone, ID, categoryid, State
 FROM users 
 GROUP BY Name, Phone, State
 HAVING repetitions > 1 
 ORDER BY ID DESC

this works but MySQL returns the first ID in a set of duplicates. For example, lets say I have 2 rows. ID for row one is 1 and ID for row two is 2 and Name, Phone and State have identical data... How can i get the above query to return the count but with the ID "2" instead of "1"?

Thanks! ;)

Answer 1

Use the max() aggregate function:

SELECT COUNT(*) AS repetitions, max(ID) FROM users GROUP BY Name, Phone, State HAVING repetitions > 1 ORDER BY ID DESC

Answer 2

not perfect but works

SELECT COUNT( * ) AS repetitions, Name, Phone, MAX(ID), categoryid, State 
FROM users 
GROUP BY Name, Phone, State 
HAVING repetitions > 1