A simple query I can’t get my brain around:

I have a table that looks like this:

Col1 Col2 Col3
1 a 1
1 b 2
1 c 3

Assume there are many more rows with different values for all columns. I want to return Col1 and Col2 based on the max value of Col3 grouped by Col1. I don’t want to aggregate Col2 but just want the value that corresponds to the aggregate max of Col3.

“SELECT Col1, Col2, MAX(Col3) GROUP BY Col1, Col2” doesn’t give me what I want and “SELECT Col1, Col2, MAX(Col3) GROUP BY Col1” isn’t allowed because Col2 isn’t aggregated or listed in the GROUP BY clause. How do I get the values of Col1 and Col2 based on the max of Col3 for the group of Col1?

Maybe this is what you want:

SELECT a.Col1, b.Col2, MAX(a.Col3) FROM mytable a, mytable b WHERE a.Col1 = b.Col1 and a.Col3 = b.Col3 GROUP BY a.Col1

try this, if you're server supports subqueries, making a left join to a subquery result.

select a.col1,a.col2,b.maxCol3 from mytable a
left join (select a2.col1,max(a2.col3) as maxCol3
from mytable a2 group by a2.col1) b on a.col1 = b.col1