How to use RANK() in SQL Server

by

Change:

RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank

to:

RANK() OVER (ORDER BY totals DESC) AS xRank

Have a look at this example:

SQL Fiddle DEMO

You might also want to have a look at the difference between RANK (Transact-SQL) and DENSE_RANK (Transact-SQL):

RANK (Transact-SQL)

If two or more rows tie for a rank, each tied rows receives the same
rank. For example, if the two top salespeople have the same SalesYTD
value, they are both ranked one. The salesperson with the next highest
SalesYTD is ranked number three, because there are two rows that are
ranked higher. Therefore, the RANK function does not always return
consecutive integers.

DENSE_RANK (Transact-SQL)

Returns the rank of rows within the partition of a result set, without
any gaps in the ranking. The rank of a row is one plus the number of
distinct ranks that come before the row in question.

More Related Contents:

  1. How do I create a unique constraint that also allows nulls?
  2. How do I get list of all tables in a database using TSQL?
  3. Get first day of week in SQL Server
  4. What is the significance of 1/1/1753 in SQL Server?
  5. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Insert Update trigger how to determine if insert or update
  8. SQL time difference between two dates result in hh:mm:ss
  9. What is the best way to truncate a date in SQL Server?
  10. Dynamic SQL (passing table name as parameter)
  11. SQL Server: Difference between PARTITION BY and GROUP BY
  12. How do you truncate all tables in a database using TSQL?
  13. Understanding SQL Server LOCKS on SELECT queries
  14. SQL Server: How to select all days in a date range even if no data exists for some days
  15. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  16. TSQL: How to convert local time to UTC? (SQL Server 2008)
  17. Find non-ASCII characters in varchar columns using SQL Server
  18. How can I determine in SQL Server if a dateTime range overlaps another
  19. How do you change the datatype of a column in SQL Server?
  20. SQL Server Query – groupwise multiplication
  21. Getting week number off a date in MS SQL Server 2005?
  22. How to reuse calculated columns avoiding duplicating the sql statement
  23. How do I move a table into a schema in T-SQL
  24. How can I insert binary file data into a binary SQL field using a simple insert statement?
  25. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  26. Include missing months in Group By query
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. @@ERROR and/or TRY – CATCH
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment