Rank ranking function in SQL

A. Ranking rows in a partition

The following example ranks the products in the list of specified inventory locations by quantity.

 locationid and logically ordered by Quantity . " > result set Press  locationid  partition and logically press  quantity  sort.

locationid and logically ordered by Quantity . " >  > Note that products 494 and 495 have the same number.  

 

Use AdventureWorks2012; Goselect I.productid, P.name, I.locationid, I.quantity,
RANK () over  (ORDER by i.quantity DESC) as Rankfrom production.productinventory as I INNER JOIN Production.pro Duct as P on  i.productid = P.productidwhere I.locationid between 3 and 4ORDER by I.locationid; GO

Here is the result set:

  

ProductID   Name                   locationid   Quantity Rank---------------------------------------------------------494         paint-silver                3       1495         paint-blue                  3       1493         paint-red                   3            41       3496         Paint-yellow              3       4492         paint-black                 3       5495         paint-blue                  4            35       1496         Paint-yellow              4       2493         paint-red                   4       3492         paint-black                 4            14       4494         Paint-silver                4       5 (s) affected)

B. Ranking all rows in the result set

The following example returns the top 10 employees by salary. because the PARTITION by clause is not specified, the RANK function applies to all rows in the result set.

  

Use Adventureworks2012select TOP (Ten) BusinessEntityID, rate, RANK () over (ORDER by rate DESC) as Rankbysalaryfrom humanres Ources. EmployeePayHistory as Eph1where ratechangedate = (SELECT MAX (ratechangedate) from                         Humanresources.employeepayhistory as Eph2                        WHERE eph1. BusinessEntityID = Eph2. BusinessEntityID) ORDER by BusinessEntityID;

Here is the result set:

  

BusinessEntityID rate                  rankbysalary---------------------------------------------------------1                125.50                63.4615                43.2692                29.8462               195                32.6923 166                32.6923               167                50.4808                40.8654               109                40.8654               1010               42.4808               9

Rank ranking function in SQL