Easy query? Not to me.

[John Lewis]

Hi,

I have a table tScores with 3 fields: user, score and stage.
What I want is to select the 2 best scores (highest) of each user.

Ex.
tScores
---------
A, 20, 1
A, 10, 2
A, 30, 3
A, 40, 4
A, 10, 5
B, 20, 1
B, 30, 2
B, 10, 3
C, 10, 1

The result would be:

A, 40, 4
A, 30, 3
B, 30, 2
B, 20, 1
C, 10, 1

Thanks in advance,

John Lewis

 

[Michel Walsh]

Hi,


SELECT a.f1, a.f2, LAST(a.f3)
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2 <= b.f2
GROUP BY a.f1, a.f2
HAVING COUNT(*) <= 2



or


SELECT f1, f2, f3
FROM myTable AS a
WHERE f2 IN( SELECT TOP 2 b.f2
FROM myTable As b
WHERE b.f1=a.f1
ORDER BY b.f2 DESC)
ORDER BY f1, f2 DESC


The first solution rank each record, for each f1, and keep those with a rank
<= 2. The second solution find the top 2 limited to a given associated
value, and check if the actual candidate is part of this set.



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Sounds like you need to use a coordinated sub-query with a TOP N clause.

SELECT User, Score, Stage
FROM [tScores]
WHERE Score in
(SELECT TOP 2 T.Score
FROM [tScores] As T
WHERE T.User = [tScores].User
ORDER BY T.Score Desc)

Note that this could return more than 2 records for a user if they have the
same high score (ties). Changing your original data sample to

A, 30, 1
A, 10, 2
A, 30, 3
A, 40, 4
A, 10, 5

Returns
A, 30, 1
A, 30, 3
A, 40, 4