January 19, 2007 at 11:03 am
SET NOCOUNT ON
DECLARE @Student TABLE
(
Class VARCHAR(10),
[Group] VARCHAR(10),
Score NUMERIC(5, 2)
)
INSERT @Student
SELECT 'Class1', 1, 100 UNION ALL
SELECT 'Class1', 2, 90 UNION ALL
SELECT 'Class1', 3, 80 UNION ALL
SELECT 'Class1', 4, 70 UNION ALL
SELECT 'Class2', 1, 100 UNION ALL
SELECT 'Class2', 2, 100 UNION ALL
SELECT 'Class2', 3, 70 UNION ALL
SELECT 'Class2', 4, 50 UNION ALL
SELECT 'Class3', 1, 100 UNION ALL
SELECT 'Class3', 2, 50 UNION ALL
SELECT 'Class3', 3, 50 UNION ALL
SELECT 'Class3', 4, 10 UNION ALL
SELECT 'Class4', 1, 100 UNION ALL
SELECT 'Class4', 2, 50 UNION ALL
SELECT 'Class4', 3, 50 UNION ALL
SELECT 'Class4', 4, 50 UNION ALL
SELECT 'Class5', 1, 100 UNION ALL
SELECT 'Class5', 2, 100 UNION ALL
SELECT 'Class5', 3, 100 UNION ALL
SELECT 'Class5', 4, 50
SELECT * FROM @Student ORDER BY 1, 3 DESC
/*
Task is to select top two groups in each class. If two or more groups scores same then select all of them.
Minimum 2 groups to be selected without being unfair to same scoring groups. Where possible don't take more than 2 groups like class 1 and class 2.
Expected results
Class Group Score
---------- ---------- -------
Class1 1 100.00
Class1 2 90.00
Class2 1 100.00
Class2 2 100.00
Class3 1 100.00
Class3 2 50.00
Class3 3 50.00
Class4 1 100.00
Class4 2 50.00
Class4 3 50.00
Class4 4 50.00
Class5 1 100.00
Class5 2 100.00
Class5 3 100.00
*/
Regards,
gova
January 19, 2007 at 11:37 am
This is on SQL2K, right, and not SQL 2005 ? (Asking because there is a simpler solution in 2005 using the new ranking functionality in T-SQL).
January 19, 2007 at 12:52 pm
It is SQL2K.
Regards,
gova
January 19, 2007 at 2:42 pm
Because of the issues with ties, I resorted to solving this with a temp table and an absolute ranking for each record. The absolute ranking allows easy retrieval of the 2nd place record within each group (even if its score is a tie for 1st).
Then get the 2nd place score, and join back to the original table, where score is greater than or equal than that 2nd place score:
-- Create an empty table for ranking, using an Identity column
Select Identity(int, 1, 1) As Rank, *
Into #Ranking
From @Student
Where 0 = 1
-- Rank records by the required order
Insert Into #Ranking (Class, [Group], Score)
Select Class, [Group], Score
From #Student
Order By Class, [Group], Score Desc
Select r1.Class, r1.[Group], r1.Score
From #Ranking As r1
Inner Join
( --Derived Table: score of the 2nd place rank within each Class
Select r2.Class, r2.Score
From #Ranking As r2
Inner Join
( -- Derived Table - 2nd place ranking within each Class
Select Class, Min(Rank) + 1 As Rank2nd
From #Ranking
Group By Class
) dt2
On ( r2.Rank = dt2.Rank2nd )
) dt1
On (r1.Class = dt1.Class And
-- Join greater than equal, to return all at the 2nd ranking score or higher
r1.Score >= dt1.Score )
Order By r1.Class, r1.Score Desc
January 20, 2007 at 10:52 am
I'm with PW... I normally use "Derived Tables", as well. And, I normally don't like correlated subqueries because they can be difficult to troubleshoot. But their use seems appropriate just to avoid the triangular joins in this case... it flies without an index and absolutely screams with an index (if a permanent table) on Class,Score...
SELECT s3.Class,s3.[Group],s3.Score
FROM @Student s3
WHERE s3.Score IN
(--Finds all scores by class that match the top 2 for each class
--and picks the top 2 from those (could be duplicate)
SELECT TOP 2 s1.Score
FROM @Student s1
WHERE s1.Score IN (--Finds top 2 distinct scores numerically for each class
SELECT DISTINCT TOP 2 s2.Score
FROM @Student s2
WHERE s2.Class = s1.Class --<<--Correlation here
ORDER BY s2.Score DESC
)
AND s1.Class = s3.Class
ORDER BY s1.Class,s1.Score DESC
)
ORDER BY s3.Class,s3.Score DESC,[Group]
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2007 at 9:01 am
Thanks a million Jeff and PW. That was really very helpful.
Regards,
gova
January 22, 2007 at 4:55 pm
Thanks for the feedback, Gova... much appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply