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:
>
> col1 col2 col3
> ----------------
> 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:
>
> col1 max(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
