Multi-column Rank in SQL Server
There probably is, but I’m slightly new to SQL Server. I need to rank/denserank a dataset, but the ranking is based on 6 columns. What I have at the moment is:
SELECT col1, col2, col3, col4, col5, col6, col7, RANK() OVER(ORDER BY col2 desc) as APPLICANT_RANK FROM myTable
So that works fine, but if there is a tie in col2, then I get two records ranked the same. What I want is if there’s a tie in col2, to see the higher number in col3, then col4, so down the line to col 6.
One Solution collect form web for “Multi-column Rank in SQL Server”
You can include multiple columns in the
order by clause in the rank function, just as you would when ordering the results of a whole query:
RANK() OVER( ORDER BY col2 desc,col3 desc, col4 desc, col5 desc, col6 desc ) as APPLICANT_RANK