Page 1 of 1

MYSQL - Select where unique

Posted: Fri Feb 25, 2011 1:37 pm
by lenton
This is my table:
pet | number
------------------
cat | 3
dog | 5
cat | 9
fish | 1
dog | 0

Is there a way I can select all from table where number is biggest and pet is unique. For example, it would only select cat with number 9 and not cat with number 3.

This is what I want to select:
pet | number
------------------
dog | 5
cat | 9
fish | 1

Thanks for your help.

Re: MYSQL - Select where unique

Posted: Fri Feb 25, 2011 2:31 pm
by Eran
Group the results by the pet

Code: Select all

SELECT pet,MAX(number) AS number FROM table_name GROUP BY pet