I have the following query
IF OBJECT_ID('tempdb..#StudentScore') IS NOT NULL
DROP TABLE #StudentScoreCREATE TABLE #StudentScore
(
groupID INT,
Student_Name NVARCHAR (50),
DateofAdmission DATE
)GOINSERT INTO #StudentScore VALUES (4,'Ali', '2019-01-21')
INSERT INTO #StudentScore VALUES (4,'Zaid', '2019-01-22')
INSERT INTO #StudentScore VALUES (2,'Mohd', '2019-06-23')
INSERT INTO #StudentScore VALUES (2,'Jack', '2019-05-24')
INSERT INTO #StudentScore VALUES (3,'John', '2019-04-01')
INSERT INTO #StudentScore VALUES (1,'Mike', '2019-02-01')
INSERT INTO #StudentScore VALUES (5,'Goerge', '2019-02-02')SELECT *, RANK() OVER(ORDER BY DateOfAdmission ASC) AS RowNumberRank
FROM #StudentScoreIF OBJECT_ID('tempdb..#StudentScore') IS NOT NULL
DROP TABLE #StudentScore
I want the output to be sorted by DateofAdmission ASC. In the above example, if this sorting is done, the smallest date belongs to group ID 4. I want the the entire group 4 to be ranked 1 since it has the dateofadmission with the smallest date
Is this possible to be achieved?
August 29, 2019 at 7:11 am
You need to get the min AdmissionDate per group first
WITH cteGroupDate AS (
SELECT *
, GroupMinDate = MIN(DateofAdmission) OVER(PARTITION BY groupID)
FROM #StudentScore
)
SELECT *
, GroupRank = DENSE_RANK() OVER ( ORDER BY GroupMinDate ASC )
, RowNumberRank = RANK() OVER ( ORDER BY GroupMinDate ASC, DateofAdmission ASC )
FROM cteGroupDate;
Thank you very much. This has really resolved the problem
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply