2.3 Rank

  •  If two or more rows tie for a rank, each tied row 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.
  • The sort order that is used for the whole query determines the order in which the rows appear in a result set.

QUERY: - RANK ( ) OVER (order_by_clause) from tbName;.

  • <order_by_clause> determines the order in which the DENSE_RANK function applies to the rows in a partition.

EXAMPLE: - SELECT *, rank () OVER (order by Grade desc) AS [Rank]

FROM customer


Example


WITH PARTITION BY CLAUSE:-

QUERY: - RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause>)

From TableName

  • <partition_by_clause> First divides the result set produced by the FROM clause into partitions, and then the DENSE_RANK function is applied to each partition. See OVER Clause (Transact-SQL) for the PARTITION BY syntax

EXAMPLE: - SELECT * , rank() OVER (partition by city order by grade desc)

AS rank FROM customer


Example 2

Last modified: Tuesday, 17 December 2019, 12:35 PM