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