MYSQL - Select where unique

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

MYSQL - Select where unique

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MYSQL - Select where unique

Post by Eran »

Group the results by the pet

Code: Select all

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