help with aggregate query

[scottydel]

Hello,

I have a table as such:

NAME RANK WIN_PERCENT DRAW_PERCENT

The table can have the same people in it multipe times, such as:

NAME LEVEL WIN_PERCENT DRAW_PERCENT

Bob 4 80% 4%
Bob 5 90% 5%
Bob 8 60% 3%

What I would like to do is return only the row in which Bob's Level is the Highest (MAX).

I wrote an aggregate query as such:

SELECT NAME, MAX(LEVEL) AS MAX_LEVEL, WIN_PERCENT, DRAW_PERCENT
FROM MYTable
GROUP BY NAME, WIN_PERCENT, DRAW_PERCENT

The problem is, as you'd expect, this query returns all rows, since I am grouping by WIN_PERCENT and DRAW_PERCENT as well.

Any ideas on what query to use to just return the row with the highest (MAX) LEVEL? i.e.

NAME LEVEL WIN_PERCENT DRAW_PERCENT

Bob 8 60% 3%

Thanks,

Scott

[Brianwarnock]

You tend to need 2 queries , one to find the max level and then one to join that back to the table to pull the rest of the data.

Brian

[ColinEssex]

Or you could try this

SELECT tblName.names, Max(tblName.Level) AS MaxOfLevel, First(tblName.WinPercent) AS FirstOfWinPercent, First(tblName.Drawpercent) AS FirstOfDrawpercent
FROM tblName
GROUP BY tblName.names;

Firstly don't call your name field "Name" - it's a reserved word.

So in the query grid the first field is the name field aggregate "GroupBy"

Next field is Level - Totals line = Max

Then WinPercent and Drawpercent both Totals line set to "First"

I tried it with several names and repeats and it came up with one row for each name showing their MAX level

Col

[Brianwarnock]

Col using First returns the first entry entered in that field for that group not necessarily the one in the max record.

Brian

[ColinEssex]

Originally Posted by Brianwarnock

Col using First returns the first entry entered in that field for that group not necessarily the one in the max record.

Brian

Not if you have the 2nd field as 'level' set to Max. I tried it using 6 different names each with several entries and it always pulled up the Max level for each name

Col

[pbaldy]

I'm with Brian. First will not reliably return the values from the same record as the one with the max level. You need the 2 query method.

[ColinEssex]

Originally Posted by pbaldy

I'm with Brian. First will not reliably return the values from the same record as the one with the max level. You need the 2 query method.

Ok, maybe I just got lucky when it pulled up all the correct records - go with Brian's method

Col