MySQL Forums
Forum List  »  General

query using group by, having, max
Posted by: dsdart
Date: November 09, 2004 05:46PM

I'd like to set up a query to get the row with the maximum value in a certain column for each name. Best explained by example. The database is set up like this:

name, code1, code2
AA, 5.0, 0
AA, 4.0, 0
AA, 5.0, 1
BB, 5.0, 0
CC, 5.0, 0
CC, 5.0, 1
CC, 5.0, 2

I'd like to get a report with the following:
AA 5.0 1
BB 5.0 0
CC 5.0 2

I'm trying "SELECT * FROM database WHERE code1='5.0' GROUP BY name HAVING max(Code2)<=2";

This gives me:
AA 5.0 0
BB 5.0 0
CC 5.0 0

I had it this morning, had to reboot, and can't figure it out again (dang it!). Can someone show me what's wrong? Hopefully I've given enough information.

thanks

Options: ReplyQuote


Subject
Written By
Posted
query using group by, having, max
November 09, 2004 05:46PM
November 10, 2004 11:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.