SQLite RANK() Function

Summary: in this tutorial, you will learn how to use the SQLite RANK() function to calculate the ranks for rows in a query’s result set.

Introduction to SQLite RANK() function

The RANK() function is a window function that assigns a rank to each row in a query’s result set. The rank of a row is calculated by one plus the number of ranks that come before it.

The following shows the syntax of the RANK() function:

RANK() OVER (
	PARTITION BY <expression1>[{,<expression2>...}]
	ORDER BY <expression1> [ASC|DESC], [{,<expression1>...}]
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, the PARTITION BY clause divides the rows of the result set into partitions.
• Second, the ORDER BY clause specifies the order of the rows in each partition.
• Third, the RANK() function is applied to each row in each partition and re-initialized when crossing the partition boundary.

The same column values will receive the same ranks. When multiple rows have the same rank, the rank of the next row is not consecutive. This is like the Olympic medal in which if two athletes share the gold medal, there will be no silver medal.

SQLite RANK() illustration

First, create a new table named RankDemo that has one column:

CREATE TABLE RankDemo (
	Val TEXT
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the RankDemo table:

INSERT INTO RankDemo(Val)
VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');Code language: SQL (Structured Query Language) (sql)

Third, query data from the RankDemo table:

SELECT * FROM RankDemo;Code language: SQL (Structured Query Language) (sql)

Fourth, use the RANK() function to assign ranks to the rows in the result set of RankDemo table:

SELECT
  Val,
  RANK() OVER (ORDER BY Val) ValRank
FROM
  RankDemo;Code language: SQL (Structured Query Language) (sql)

Here is the output:

The output shows that the third and fourth rows receive the same rank because they have the same value. The fifth row gets the rank 5 because the RANK() function skips the rank 4.

SQLite RANK() function examples

To demonstrate the RANK() function, we will use the tracks table from the sample database.

1) Using RANK() function with ORDER BY clause example

The following statement uses the RANK() function to rank tracks by their lengths:

SELECT
	Name,
	Milliseconds,
	RANK () OVER ( 
		ORDER BY Milliseconds DESC
	) LengthRank 
FROM
	tracks;Code language: SQL (Structured Query Language) (sql)

In this example, we skipped the PARTITION BY clause, therefore, the RANK() function treats the whole result set as a single partition.

First, the ORDER BY clause sorts the tracks by their lengths (Milliseconds column).

Second, the RANK() function is applied to each row in the result set considering the orders of tracks by their lengths.

2) Using the RANK() function with PARTITION BY example

The following statement uses the RANK() function to assign a rank to each track in each album:

SELECT
  Name,
  Milliseconds,
  AlbumId,
  RANK() OVER  (
    PARTITION BY
      AlbumId
    ORDER BY
      Milliseconds DESC
  ) LengthRank
FROM
  tracks;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this example:

• First, the PARTITION BY clause divides the tracks into albums.
• Then, the ORDER BY clause sorts the tracks by their lengths.
• Finally, the RANK() function assigns a rank to each track in each album. If the album changes, the RANK() function re-initializes the rank value.

It is possible to find the second-longest track in each album by using the following subquery:

SELECT
  *
FROM
  (
    SELECT
      Name,
      Milliseconds,
      AlbumId,
      RANK() OVER  (
        PARTITION BY
          AlbumId
        ORDER BY
          Milliseconds DESC
      ) LengthRank
    FROM
      tracks
  )
WHERE
  LengthRank = 2;Code language: SQL (Structured Query Language) (sql)

The output is as follows:

Summary

• Use the RANK() function to assign a rank to each row in a result set.