Nested Query

[Oleg]

my least favorite query, and i am always having trouble with it

COMBO ORDER VALUE
x 4 45
x 3 54
y 2 12
y 8 68


I need to bring back MAX(ORDER) and the VALUE that belongs to that order - so for x - 4 and 45 and for y 8 and 68

 

[Brianwarnock]

I always use 2 queries the first to group on the identifier ie Combo and select Max on the required field ie Order then join this query to the original data on these fields to use the result to pull any other data.

Brian

 

[jdraw]

Here's another

SELECT combo ,Order, value
FROM Oleg
WHERE oleg.value IN
(SELECT TOP 1 Value
FROM Oleg AS Dup
WHERE Dup.combo = Oleg.combo
ORDER BY Dup.Order DESC, Dup.value DESC)
ORDER BY oleg.combo,Oleg.order , Oleg.value

 

[Oleg]

I always use 2 queries the first to group on the identifier ie Combo and select Max on the required field ie Order then join this query to the original data on these fields to use the result to pull any other data.

Brian

hey Brian -
thats what i ended up doing, but back in the old days i was able to d oit in 1 query.. I guess 2 is fine