max value based on two columns

[JustATheory]

Greetings,

I'm stuck on a simple query to find max values, I'm looking for the max number in col1 based on the values in col2. I keep getting all values. I thought it was as simple as:

select max(col1), col2 from table;

I've tried subqueries with a similar result. Help is always greatly appreciated.

Thanks,
JustATheory

col1 col2
10 3333
15 3333
5 4444
6 4444
7 4444

expected result

col1 col2
15 3333
7 4444

 

[carp]

Try:

SELECT col2, MAX(col1)
FROM my table
GROUP BY col2;