CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 23

How to rank sql query

I have a set of results that I want to rank:

I am not sure what to partition by.

Query:

SELECT DISTINCT 
    TFormSectionID AS FormSectionID, 
    TFSSortOrder AS SectionSortOrder, 
    TSectionItemID AS SectionItemID, TrendType
FROM            
    Report.TrendData
WHERE        
    (ProgramID = 1) 
    AND (TrendType > 0) 
    AND tformid = 34 
    AND TFormSectionID = 12

Results:

FormSectionID   SectionSortOrder    SectionItemID   TrendType
    12                 7                  90            1
    12                 7                  91            1
    12                 7                  154           1
    12                 7                  528           1
    12                 9                  154           1
    12                 9                  528           1

I want the results with section sort order 9 to be ranked as 2 and the section sort order 7 to be ranked as 1

Upvotes: 0

Views: 68

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

It seems like you can get what you want using DENSE_RANK:

SELECT DISTINCT TFormSectionID AS FormSectionID, 
       TFSSortOrder AS SectionSortOrder, 
       TSectionItemID AS SectionItemID, 
       TrendType,
       DENSE_RANK() OVER (ORDER BY SectionSortOrder) AS rn       
FROM Report.TrendData
WHERE (ProgramID = 1) AND (TrendType > 0) AND 
      tformid = 34 and TFormSectionID = 12

Upvotes: 1

Related Questions