CODEWITHSUNDEEP
sqlsql-server
Ashing
Ashing

Reputation: 79

Ranking of rows depends on the number of duplicates

Is it possible to do this in MSSQL? I already search for rank built in functions but it didn't get this kind of ranking. 

Term    Course_ID  Description   Grade   Rank
1601    001173     Gen Math       76      1 
1601    001235     21st Cent      79      1
1601    001235     21st Cent      73      2
1601    001247     Philosophy     81      1
1601    001247     Philosophy     71      2
1601    001259     Oral Com       76      1
1601    001548     English        85      1

I have to set the rank to 1 for the first record of that particular Course_Id and continue to count until the last duplicate and reset to 1 for another course_ID.

Thanks in advance.

Upvotes: 0

Views: 70

Answers (3)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

DECLARE @Test TABLE (
    Term        INT,
    Course_ID   VARCHAR(32),
    Description VARCHAR(100),
    Grade       INT
)

INSERT @Test
VALUES
    (1601,    '001173',     'Gen Math',       76),
    (1601,    '001235',     '21st Cent',      79),
    (1601,    '001235',     '21st Cent',      73),
    (1601,    '001247',     'Philosophy',     81),
    (1601,    '001247',     'Philosophy',     71),
    (1601,    '001259',     'Oral Com',       76),
    (1601,    '001548',     'English',        85)

SELECT ROW_NUMBER() OVER (PARTITION BY Course_ID ORDER BY Term) [Rank], Term, Course_ID, Description, Grade
FROM @Test

Upvotes: 1

Stephen Whitfield
Stephen Whitfield

Reputation: 81

I think this could be solved with a Rank function Partition By the Course_ID. Please see my exampled here

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use Row_Number() function instead of Rank() as below :

SELECT *,
       ROW_NUMBER() OVER(PARTITION BY Course_ID ORDER BY Course_ID) Rank
FROM <table_name>;

Output :

Term    Course_ID  Description   Grade   Rank
1601    001173     Gen Math       76      1 
1601    001235     21st Cent      79      1
1601    001235     21st Cent      73      2
1601    001247     Philosophy     81      1
1601    001247     Philosophy     71      2
1601    001259     Oral Com       76      1
1601    001548     English        85      1

Upvotes: 2

Related Questions