SQL RANK() vs DENSE_RANK(): Differences & Examples

Several managers in the Playground have the same number of direct reports, so ranking managers by team size produces ties. The query below ranks managers by team size with all three ranking functions:

WITH team AS (
  SELECT manager_id, COUNT(*) AS reports
  FROM employees
  WHERE manager_id IS NOT NULL
  GROUP BY manager_id
)
SELECT manager_id, reports,
       ROW_NUMBER() OVER (ORDER BY reports DESC, manager_id) AS row_num,
       RANK()       OVER (ORDER BY reports DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY reports DESC) AS dense_rnk
FROM team
ORDER BY reports DESC, manager_id
LIMIT 8;

▶ Run this query in the SQL Playground

Read down the three columns. Wherever two managers have the same number of reports, rnk and dense_rnk repeat a value while row_num keeps counting. After the tie, rnk jumps ahead to match the row position and dense_rnk simply continues with the next number.

RANK()       OVER ([PARTITION BY group_column] ORDER BY sort_column [DESC])
DENSE_RANK() OVER ([PARTITION BY group_column] ORDER BY sort_column [DESC])

Both functions take no arguments. The ORDER BY inside OVER is what defines the ranking: rows with the same ORDER BY values are ties. They are part of the window functions family and work in PostgreSQL, SQL Server, Oracle, MySQL 8.0+ and SQLite 3.25+.

Add PARTITION BY to rank inside each group. This ranks products by rating within their category:

SELECT category, name, rating,
       RANK() OVER (PARTITION BY category ORDER BY rating DESC) AS rating_rank
FROM products
WHERE category IN ('Books', 'Toys')
ORDER BY category, rating_rank, name
LIMIT 10;

▶ Run this query in the SQL Playground

The rank starts again at 1 for each category, and products with the same rating share a rank.

"Find the second (or third) highest salary" is a classic interview question. DENSE_RANK() answers it directly, because its ranks count distinct values:

WITH ranked AS (
  SELECT first_name, last_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT first_name, last_name, salary
FROM ranked
WHERE rnk = 3;

▶ Run this query in the SQL Playground

Use DENSE_RANK() here rather than RANK(). If two people shared the top salary, RANK() would number the rows 1, 1, 3 and "rank 2" would not exist at all.

When everyone tied for a place should be included, filter on a rank instead of a row number. This returns each department's top two salary levels:

WITH ranked AS (
  SELECT department, first_name, last_name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, first_name, last_name, salary, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY department, rnk, last_name
LIMIT 8;

▶ Run this query in the SQL Playground

Three errors that are easy to miss.

RANK() OVER (ORDER BY salary DESC) AS rnk
-- then WHERE rnk = 2

DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
-- then WHERE rnk = 2

RANK() OVER (ORDER BY salary)

RANK() OVER (ORDER BY salary DESC)

SELECT name,
       RANK() OVER (ORDER BY price DESC) AS rnk
FROM products
WHERE rnk <= 3;

WITH ranked AS (
  SELECT name,
         RANK() OVER (ORDER BY price DESC) AS rnk
  FROM products
)
SELECT name, rnk
FROM ranked
WHERE rnk <= 3;

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Rank products by review_count, highest first, with RANK(). Show the first eight rows. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, review_count,
          RANK() OVER (ORDER BY review_count DESC) AS rnk
   FROM products
   ORDER BY rnk, name
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   Products with the same review_count share a rank.

2. 2Medium Find the employees with the second highest salary in the company. Table: employees

   Show solution

   SQLite · runs in the Playground

   WITH ranked AS (
     SELECT first_name, last_name, salary,
            DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
     FROM employees
   )
   SELECT first_name, last_name, salary
   FROM ranked
   WHERE rnk = 2;

   ▶ Run this query in the SQL Playground

   DENSE_RANK() counts distinct salary values, so rank 2 is always the second highest value.

3. 3Medium Rank shipping cities by their number of orders and show the top eight. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT shipping_city, COUNT(*) AS orders,
          RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
   FROM orders
   GROUP BY shipping_city
   ORDER BY rnk, shipping_city
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   A window function can rank the result of GROUP BY: the aggregate is calculated first and then ranked.

4. 4Challenging For each department, return the employees with the highest salary, including ties. Table: employees

   Show solution

   SQLite · runs in the Playground

   WITH ranked AS (
     SELECT department, first_name, last_name, salary,
            RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
     FROM employees
   )
   SELECT department, first_name, last_name, salary
   FROM ranked
   WHERE rnk = 1
   ORDER BY department;

   ▶ Run this query in the SQL Playground

   Filtering on rank 1 keeps every employee who shares the top salary in a department: 10 rows.