Need to find the 2 highest numbers for each person?

[pokdbz]

I need to find the 2 highest numbers for each person in a table.

Is there a simple way to write a query to do this?

Eg.
person score
#1 10
#1 9
#1 8

So it would pull
#1 10 and 9 for scores

 

[Barry Gilbert]

You can use the Top predicate. The query designer is a little inflexible with
this, so you'll need to edit the SQL view. It should look something like this:

SELECT TOP 2 Person, Score FROM MyTable Order By Score;

The TOP keyword will work against the column that the query is sorted by.
You can get more details about this in Access help.

Barry

 

[pokdbz]

This works for just one person:
SELECT TOP 2 Table1.person, Table1.score
FROM Table1
ORDER BY Table1.score DESC;

I don't know how to get it to work with multiple people

 

[Barry Gilbert]

Sorry. I didn't catch that. You'll need a subquery. Something like this:

SELECT Score, Person FROM Table1 WHERE table1.Score IN(SELECT TOP 2 t.Score
FROM Table1 AS t WHERE t.Person = Table1.Person ORDER BY Score) GROUP BY
Person, Score;

Barry

 

[pokdbz]

Its pulling 2 scores for each person which is good. But it is not pulling
the 2 highest.

SELECT Table1.score, Table1.person
FROM Table1
WHERE (((Table1.score) In (SELECT TOP 2 t.Score
FROM Table1 AS t WHERE t.Person = Table1.Person ORDER BY Score)))
GROUP BY Table1.score, Table1.person;

 

[Barry Gilbert]

Oops. After ORDER BY Score in the subquery, add DESC.

Barry

 

[pokdbz]

Excellent, worked perfect.

Oops. After ORDER BY Score in the subquery, add DESC.

Barry