October 4, 2007 at 11:48 am
To simplify the problem, I have a table with two columns: ID and SCORE. An ID may have multiple SCOREs. My question is how to make a query or batch to select nth best socres for each ID?
For example the table may contain
ID SCORE
1 10
2 5
2 15
2 25
1 20
3 1
3 1
3 2
3 4
If I want 3rd best score (assume the smaller the better), I will get
ID SCORE
1 NULL
2 25
3 4
after the query
Thanks!
hshen
October 4, 2007 at 12:28 pm
October 4, 2007 at 9:09 pm
Thanks for your response.
The results from searching "top nth" seem to deal with a query that find the nth row for whole table. What I want is to find the nth SCORE for each ID, not the nth SCORE for whole table.
October 5, 2007 at 3:35 am
Is this what you are after?
CREATE TABLE #Scores(
TID int identity(1,1),--PK
ID int,
Score int)
INSERT INTO #Scores(ID,Score) SELECT 1,10
INSERT INTO #Scores(ID,Score) SELECT 2,5
INSERT INTO #Scores(ID,Score) SELECT 2,15
INSERT INTO #Scores(ID,Score) SELECT 2,25
INSERT INTO #Scores(ID,Score) SELECT 1,20
INSERT INTO #Scores(ID,Score) SELECT 3,1
INSERT INTO #Scores(ID,Score) SELECT 3,1
INSERT INTO #Scores(ID,Score) SELECT 3,2
INSERT INTO #Scores(ID,Score) SELECT 3,4
SELECT
S1.ID,
S1.Score,
1 + (SELECT COUNT(DISTINCT(S2.Score))
FROM #Scores S2
WHERE S2.Score<S1.Score
AND S1.ID=S2.ID) AS 'Rank'
FROM #Scores S1
GROUP BY S1.ID,S1.Score
ORDER BY S1.Id,[Rank]
DROP TABLE #Scores
Output looks like;
ID SCORE RANK
1 10 1
1 20 2
2 5 1
2 15 2
2 25 3
3 1 1
3 2 2
3 4 3
Low score is better of course...
Cheers!
October 5, 2007 at 9:26 am
a
October 5, 2007 at 7:53 pm
Taking one step futher on the query you give, I get what I want
SELECT S3.ID, S3.Score
FROM
(SELECT
S1.ID,
S1.Score,
1 + (SELECT COUNT(DISTINCT(S2.Score))
FROM #Scores S2
WHERE S2.Score<S1.Score
AND S1.ID=S2.ID) AS 'Rank'
FROM #Scores S1
GROUP BY S1.ID,S1.Score
ORDER BY S1.Id,[Rank]
)S3
WHERE S3.Rank = @nth
When @nth=2, the output will be
ID SCORE RANK
1 20
2 15
3 2
Many thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply