Grouping question

[JDTWorld]

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?

[Faris]

Hi JDTWorld,

Not sure if it is the most effective solution, but the below code should work given the example you gave.

SELECT 
	parent.col1, 
	parent.col2, 
	MAX(parent.col3) 
FROM 
	<your_table> AS parent
WHERE 
	parent.col3 = (SELECT MAX(child.col3) 
			from <your_table> AS child 
			where child.col1 = parent.col1)
GROUP BY 
	parent.col1, 
	parent.col2

Faris

[JDTWorld]

Works like a champ. Thanks for your insight.

[Faris]

No worries, glad I could help.

Faris