May 27, 2009 at 3:51 am
DECLARE @Table TABLE
(
SubmissionId INT,
AssessmentId INT,
AttemptNo INT,
ObtainedMarks INT,
Attempteddate DATETIME,
Preference TINYINT -- 1 - Hihest Score,2- LowestScore ,3- FirstAttempt,4- LastAttempt,5 -Avg
)
INSERT INTO @Table VALUES(1,1,1,34,GETDATE(),1)
INSERT INTO @Table VALUES(1,1,2,34,DATEADD(dd,1,GETDATE()),1)
INSERT INTO @Table VALUES(1,1,3,26,DATEADD(dd,2,GETDATE()),1)
INSERT INTO @Table VALUES(2,2,1,10,GETDATE(),3)
INSERT INTO @Table VALUES(2,2,2,34,DATEADD(dd,1,GETDATE()),3)
INSERT INTO @Table VALUES(2,2,3,26,DATEADD(dd,2,GETDATE()),3)
INSERT INTO @Table VALUES(2,2,4,33,DATEADD(dd,2,GETDATE()),3)
INSERT INTO @Table VALUES(3,40,1,75,DATEADD(dd,1,GETDATE()),4)
INSERT INTO @Table VALUES(3,40,2,71,DATEADD(dd,2,GETDATE()),4)
INSERT INTO @Table VALUES(4,45,1,66,DATEADD(dd,1,GETDATE()),2)
INSERT INTO @Table VALUES(4,45,2,55,DATEADD(dd,2,GETDATE()),2)
SELECT * FROM @Table
Select * from (
SELECT * ,
case
when Preference = 1 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks desc)
when Preference = 2 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Asc)
when Preference = 3 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Asc)
when Preference = 4 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Desc)
else
Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Desc)
end As Ranks
FROM @Table
) as S WHERE Ranks=1
Is it possible to get other queries with better performance?If so how?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply