SQLite dense_rank() Function

The SQLite dense_rank() function returns the rank within the partition in which the current row is located, starting at 1, with no gap.

That is, the same value has the same rank, but the rank of the next different value increases in order. For example, if there are 2 first places, then the rank of third place is 2. This is different from the rank() function.

`dense_rank()` Syntax

Here is the syntax of the SQLite dense_rank() function:

dense_rank()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

Parameters

partition_column_list: List of columns for partitioning.
partition_column_list: List of columns for sorting.

Return value

The SQLite dense_rank() function returns the rank within the partition in which the current row is located, starting at 1, with no gap.

`dense_rank()` Examples

Preparing Data

Use the following CREATE TABLE statement to create a table named student_grade to store grades of students:

CREATE TABLE student_grade (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class CHAR(1) NOT NULL,
  subject VARCHAR(20) NOT NULL,
  grade INT NOT NULL
);

This student_grade table has 5 columns as following:

id - The row ID, primary key.
name - The name of a student.
class - The class a student is in.
subject - The name of a subject.
grade - The grades for a subject and a student.

Insert some rows into the student_grade table using the following INSERT statement:

INSERT INTO student_grade
  (name, class, subject, grade)
VALUES
  ('Tim', 'A', 'Math', 9),
  ('Tom', 'A', 'Math', 7),
  ('Jim', 'A', 'Math', 8),
  ('Tim', 'A', 'English', 7),
  ('Tom', 'A', 'English', 8),
  ('Jim', 'A', 'English', 7),
  ('Lucy', 'B', 'Math', 8),
  ('Jody', 'B', 'Math', 6),
  ('Susy', 'B', 'Math', 9),
  ('Lucy', 'B', 'English', 6),
  ('Jody', 'B', 'English', 7),
  ('Susy', 'B', 'English', 8);

Use the following SELECT statement to show all rows in this table:

SELECT * FROM student_grade;

id  name  class  subject  grade
--  ----  -----  -------  -----
1   Tim   A      Math     9
2   Tom   A      Math     7
3   Jim   A      Math     8
4   Tim   A      English  7
5   Tom   A      English  8
6   Jim   A      English  7
7   Lucy  B      Math     8
8   Jody  B      Math     6
9   Susy  B      Math     9
10  Lucy  B      English  6
11  Jody  B      English  7
12  Susy  B      English  8

Example 1

To show the rank of each student in each subject in descending order of grade, use the following statement:

SELECT *,
  dense_rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) dense_rank,
  rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) rank
FROM student_grade;

id  name  class  subject  grade  dense_rank  rank
--  ----  -----  -------  -----  ----------  ----
5   Tom   A      English  8      1           1
12  Susy  B      English  8      1           1
4   Tim   A      English  7      2           3
6   Jim   A      English  7      2           3
11  Jody  B      English  7      2           3
10  Lucy  B      English  6      3           6
1   Tim   A      Math     9      1           1
9   Susy  B      Math     9      1           1
3   Jim   A      Math     8      2           3
7   Lucy  B      Math     8      2           3
2   Tom   A      Math     7      3           5
8   Jody  B      Math     6      4           6

Note that the window function in the SQL statement above:

dense_rank() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

In the OVER clause,

The PARTITION BY subject partitions all rows by subject
The ORDER BY grade DESC sorts all rows within each partition in descending order by grade.
The dense_rank() returns the rank of each row within its associated partition.

You can also get the difference between dense_rank() and rank() from above.

Example 2

To show the rank of each student in each class in descending order of overall grade, use the following statement:

SELECT
  t.*,
  dense_rank() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) dense_rank
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

class  name  sum_grade  dense_rank
-----  ----  ---------  ----------
A      Tim   16         1
A      Jim   15         2
A      Tom   15         2
B      Susy  17         1
B      Lucy  14         2
B      Jody  13         3

Notice this subquery in the above statement:

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

This subquery uses the GROUP BY clause and the sum() function sums up each student’s total grade by class and student.

class  name  sum_grade
-----  ----  ---------
A      Jim   15
A      Tim   16
A      Tom   15
B      Jody  13
B      Lucy  14
B      Susy  17

The main statement partitions all rows from this subquery by classes, then sort by total grade in descending order within each partition, and returns the rank of each row within its associated partition using dense_rank().

dense_rank() Syntax