Max Score Details

[Paul W Smith]

I have the a query which produces the following results:

100 A Z 1/1/2006 Orange
Dog
99 A Y 2/1/2006 Red
Duck
105 A X 3/1/2006 Green
Cat
102 B Y 2/1/2006 Blue
Cat
106 B X 1/1/2006 Red
Dog
98 B Z 3/1/2006 Orange
Duck
101 C Z 1/1/2006 Blue
Dog
96 C X 3/1/2006 Green
Cat
98 C Y 2/1/2006 Green
Cat


I want to extract from this a refined query showing the line for each entity
in the second column, where the figure in the first column is the maximum.
I need the whole line and this is my problem using 'Max' and group by does
not seem to refine anything it just returns everything.

 

[Marshall Barton]

I have the a query which produces the following results:

100 A Z 1/1/2006 Orange
Dog
99 A Y 2/1/2006 Red
Duck
105 A X 3/1/2006 Green
Cat
102 B Y 2/1/2006 Blue
Cat
106 B X 1/1/2006 Red
Dog
98 B Z 3/1/2006 Orange
Duck
101 C Z 1/1/2006 Blue
Dog
96 C X 3/1/2006 Green
Cat
98 C Y 2/1/2006 Green
Cat


I want to extract from this a refined query showing the line for each entity
in the second column, where the figure in the first column is the maximum.
I need the whole line and this is my problem using 'Max' and group by does
not seem to refine anything it just returns everything.

SELECT T.*
FROM yourquery As T
WHERE T.firstcol = (
SELECT Max(X.firstcol)
FROM yourquery As X
WHERE X.secondcol = T.secondcol)