Without using a subquery, you have to do it in two steps. First you need
to collect the MAX(col2) values for each col1 value:
CREATE TEMPORARY TABLE tmpMaxes
SELECT col1, max(col2)
from test_table
GROUP BY col1
Then you want to get the rows where the col2 value is the maximum for each
col1 value (as we determined in the last query):
SELECT tt.col1, tt.col2, tt.col3
FROM test_table tt
INNER JOIN tmpMaxes tm
ON tm.col1 = tt.col1
AND tm.col2 = tt.col2
Finally, you should always clean up after yourself:
DROP TABLE tmpMaxes
Make sense?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED] wrote on 07/28/2004 12:29:26 PM:
Hi,
I'm using MySQL 4.0.18 on Win2k Server. I have a table similar to the
following:
col1col2col3
A 1 this
A 2 is
A 3 a
B 1 test
B 2 table
What I would like to get with one query is the following result:
col1max(col2) col3
--
A 3 a
B 2 table
I've searched around, but haven't been able to come up with anything.
This
query doesn't give me the correct results:
select col1, max(col2), col3 from table group by col1
Any suggestions?
Thanks in advance,
Matt
