Need help constructing MAX() query ...

[Anon]

Hi, I need help building a select query that gets the value of 3 columns (col1,col2,col3) where col4 is the maximum value of all records that have a distinct value for col1.

For example if my table contained ...

col1 col2 col3 col4

A Joe cat 13
A Pat dog 7
A Jen cat 3
B Bill dog 33
B Tim dog 24

the query would return ...

A Joe cat
B Bill dog

.. since those two records have the maximum value for col4 when compared to all others with the same col1. I hope I explained this right, any help appreciated!

[sarahk]

select col1+col2+col3, col1, col2, col3, max(col4)
from mytable
order by 1
group by 1

the concatenation gives you a \"key\", discard after use.

Sarah

[Anon]

Thanks but it does not work. I assume by

order by 1
group by 1

you meant

order by col1
group by col1

but either way it does not work. It returns the correct col1 and col3 but col2 does not belong to the record that contains the max(col4). Any ideas?