These are some of the ranking functions examples:
Ranking example 1—The SQL query that follows finds the male and female employees from California, and ranks them in descending order according to salary.
SELECT Surname, Sex, Salary, RANK() OVER (
ORDER BY Salary DESC) as RANK FROM Employees
WHERE State IN ('CA') AND DepartmentID =200
ORDER BY Salary DESC;
The results from the above query:
Surname Sex Salary RANK ------- --- ------ ---- Savarino F 72300.000 1 Clark F 45000.000 2 Overbey M 39300.000 3
Ranking example 2—Using the query from the previous example, you can change the data by partitioning it by gender. The following example ranks employees in descending order by salary and partitions by gender:
SELECT Surname, Sex, Salary, RANK() OVER (PARTITION BY Sex
ORDER BY Salary DESC) AS RANK FROM Employees
WHERE State IN ('CA', 'AZ') AND DepartmentID IN (200, 300)
ORDER BY Sex, Salary DESC;
The results from the above query:
Surname Sex Salary RANK ------- --- --------- ---- Savarino F 72300.000 1 Jordan F 51432.000 2 Clark F 45000.000 3 Coleman M 42300.000 1 Overbey M 39300.000 2
Ranking example 3—This example ranks a list of female employees in California and Texas in descending order according to salary. The PERCENT_RANK function provides the cumulative total in descending order.
SELECT Surname, Salary, Sex, CAST(PERCENT_RANK() OVER
(ORDER BY Salary DESC) AS numeric (4, 2)) AS RANK
FROM Employees WHERE State IN ('CA', 'TX') AND Sex ='F'
ORDER BY Salary DESC;
The results from the above query:
Surname salary sex RANK --------- --------- --- ---------- Savarino 72300.000 F 0.00 Smith 51411.000 F 0.33 Clark 45000.000 F 0.66 Garcia 39800.000 F 1.00
Ranking example 4—You can use the PERCENT_RANK function to find the top or bottom percentiles in the data set. This query returns male employees whose salary is in the top five percent of the data set.
SELECT * FROM (SELECT Surname, Salary, Sex,
CAST(PERCENT_RANK() OVER (ORDER BY salary DESC) as
numeric (4, 2)) AS percent
FROM Employees WHERE State IN ('CA') AND sex ='F' ) AS
DT where percent > 0.5
ORDER BY Salary DESC;
The results from the above query:
Surname salary sex percent --------- ---------- --- --------- Clark 45000.000 F 1.00
Ranking example 5—This example uses the ROW_NUMBER function to return row numbers for each row in all window partitions. The query partitions the Employees table by department ID, and orders the rows in each partition by start date.
SELECT DepartmentID dID, StartDate, Salary , ROW_NUMBER()OVER(PARTITION BY dID ORDER BY StartDate) FROM Employees ORDER BY 1,2;
The results from the above query are:
dID StartDate Salary Row_number() ======== =========== ========== ============= 100 1984-08-28 47500.000 1 100 1985-01-01 62000.500 2 100 1985-06-17 57490.000 3 100 1986-06-07 72995.000 4 100 1986-07-01 48023.690 5 ... ... ... ... 200 1985-02-03 38500.000 1 200 1985-12-06 54800.000 2 200 1987-02-19 39300.000 3 200 1987-07-10 49500.000 4 ... ... ... ... 500 1994-02-27 24903.000 9