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.

  • Multiple Simultaneous SQL Connection Timeouts In Multithreaded Windows Service
  • NOLOCK vs. Transaction Isolation Level
  • CLR Stored Procedures: how to set the schema/owner?
  • ASP parameters to call a stored procedure in SQL Server
  • sql query calculating no of employees joined each financial year i.e from 1-04-2002 to 31-03-2003
  • MS SQL 2008 - Create a copy of the database without the data
  • Thanks

  • DENSE_RANK() without duplication
  • 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 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.