Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » OLAP support » Window functions in SQL Anywhere » Window ranking functions

 

RANK function

You use the RANK function to return the rank of the value in the current row as compared to the value in other rows. The rank of a value reflects the order in which it would appear if the list of values was sorted.

When using the RANK function, the rank is calculated for the expression specified in the window's ORDER BY clause. If the ORDER BY clause includes multiple expressions, the second and subsequent expressions are used to break ties if the first expression has the same value in adjacent rows. NULL values are sorted before any other value (in ascending sequence).

Example 1

The following query determines the three most expensive products in the database. A descending sort sequence is specified for the window so that the most expensive products have the lowest rank, that is, rankings start at 1.

SELECT Top 3 *
       FROM ( SELECT Description, Quantity, UnitPrice,
              RANK() OVER ( ORDER BY UnitPrice DESC ) AS Rank 
              FROM Products ) AS DT
ORDER BY Rank;

This query returns the following result:

Description Quantity UnitPrice Rank
1 Zipped Sweatshirt 32 24.00 1
2 Hooded Sweatshirt 39 24.00 1
3 Cotton Shorts 80 15.00 3

Note that rows 1 and 2 have the same value for Unit Price, and therefore also have the same rank. This is called a tie.

With the RANK function, the rank value jumps after a tie. For example, the rank value for row 3 has jumped to three instead of 2. This is different from the DENSE_RANK function, where no jumping occurs after a tie. See DENSE_RANK function.

Example 2

The following SQL query finds the male and female employees from Utah and ranks them in descending order according to salary.

SELECT Surname, Salary, Sex,
     RANK() OVER ( ORDER BY Salary DESC ) "Rank"
     FROM Employees 
WHERE State IN ( 'UT' );

The table that follows represents the result set from the query:

Surname Salary Sex Rank
1 Shishov 72995.00 F 1
2 Wang 68400.00 M 2
3 Cobb 62000.00 M 3
4 Morris 61300.00 M 4
5 Diaz 54900.00 M 5
6 Driscoll 48023.69 M 6
7 Hildebrand 45829.00 F 7
8 Goggin 37900.00 M 8
9 Rebeiro 34576.00 M 9
10 Bigelow 31200.00 F 10
11 Lynch 24903.00 M 11
Example 3

You can partition your data to provide different results. Using the query from Example 2, 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, Salary, Sex,
     RANK ( ) OVER ( PARTITION BY Sex
     ORDER BY Salary DESC ) "Rank"
     FROM Employees 
WHERE State IN ( 'UT' );

The table that follows represents the result set from the query:

Surname Salary Sex Rank
1 Wang 68400.00 M 1
2 Cobb 62000.00 M 2
3 Morris 61300.00 M 3
4 Diaz 54900.00 M 4
5 Driscoll 48023.69 M 5
6 Goggin 37900.00 M 6
7 Rebeiro 34576.00 M 7
8 Lynch 24903.00 M 8
9 Shishov 72995.00 F 1
10 Hildebrand 45829.00 F 2
11 Bigelow 31200.00 F 3

For more information about the syntax for the RANK function, see RANK function [Ranking].