Optimizing query

[raymondj]

I'm trying to optimize the following query:

SELECT class.ID, class.name, values.number
from class, values
where class.ID=values.ID
order by values.number DESC

This gives me something like this (really much more rows than are shown)

14 John 96
4 Chris 95
13 Jeff 95
17 Susan 93
14 John 92
4 Susan 90
15 Mary 90
13 Jeff 88

What I would like is one unique value but the highest for each ID. I've tried many variations, but have not been able to get the following:

14 John 96
4 Chris 95
13 Jeff 95
17 Susan 93
15 Mary 90

How can I do it in MySQL?

rj

 

[swampBoogie]

SELECT max(class.ID) as x, class.name, max(values.number) as z
from class, values 
where class.ID=values.ID
group by class.name
order by z DESC

 

[raymondj]

Great! It works!
I only wanna add more columns from table values, but don't seem able to do so:

SELECT max(class.ID) as x, class.name, max(values.number) as z, values.othercolumn from ...

What is wrong?

rj

 

[tlubi]

you must list every column that isn't an aggregate in the group by-Clause

SELECT max(class.ID) as x, class.name, max(values.number) as z, values.othercolumn from ... ... group by class.name, values.othercolumn

 

[r937]

i love it when the real question turns out to be a different question, eh

raymond, have a look at this:

3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

if you're on mysql 4.1 you can use a subquery, if not, you can use a temp table


rudy