SQL RANK

What is the SQL RANK window function?

RANK returns the rank of each row within a partition of a result set, with gaps for ties.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RANK: PostgreSQL, MySQL 8+, MariaDB 10.2+, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite 3.25+, DB2

SQL RANK Full Explanation

RANK is a window (analytic) function that assigns an integer rank to each row based on the ORDER BY clause of its OVER() window specification. The smallest ORDER BY value gets rank 1. If two or more rows tie, they receive the same rank and the next rank is incremented by the number of tied rows, leaving gaps (1,1,3 …). RANK works after the WHERE, GROUP BY, and HAVING phases but before the final ORDER BY of the entire query, meaning it sees the filtered, grouped result set.Because RANK is computed per window, you can optionally segment the data with PARTITION BY so each partition restarts at rank 1. Without PARTITION BY the entire result set is treated as one partition. RANK never changes underlying data; it only adds a derived column that can be used for reporting, pagination, or further filtering. Unlike ROW_NUMBER, RANK skips numbers on ties; unlike DENSE_RANK, it leaves gaps.RANK conforms to SQL:2003 and is widely supported, but exact syntactic details vary slightly by database (e.g., required ORDER BY direction, NULL ordering rules).

SQL RANK Syntax

RANK() OVER (
    [PARTITION BY partition_expression [, ...]]
    ORDER BY sort_expression [ASC | DESC] [, ...]
)

SQL RANK Parameters

- PARTITION BY (column_expression list) - Optional. Defines groups whose ranking restarts at 1.
- ORDER BY (column_expression list) - Required. Determines sort priority inside each partition.

Example Queries Using SQL RANK

--Basic ranking of entire set
SELECT order_id,
       total_amount,
       RANK() OVER (ORDER BY total_amount DESC) AS sales_rank
FROM   orders;

--Ranking within each customer
SELECT customer_id,
       order_id,
       total_amount,
       RANK() OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS customer_order_rank
FROM orders;

--Filter top 3 products per category
WITH ranked AS (
  SELECT category,
         product_id,
         revenue,
         RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS r
  FROM   product_sales
)
SELECT *
FROM   ranked
WHERE  r <= 3;

Expected Output Using SQL RANK

Each query appends a new column (sales_rank, customer_order_rank, or r) showing integer ranks
Rows with identical ORDER BY values share the same rank; subsequent ranks jump accordingly

Use Cases with SQL RANK

Produce leaderboard-style reports (top customers, top products)
Paginate ordered results when you need deterministic ties handling
Pick N highest or lowest values per group without subqueries
Compare each row's standing inside its group for anomaly detection

Common Mistakes with SQL RANK

Omitting ORDER BY inside OVER() – results in an error.
Expecting sequential numbers with ties (use DENSE_RANK or ROW_NUMBER instead).
Applying ORDER BY outside the window only – this does not influence ranking.
Forgetting PARTITION BY when separate groups are intended, leading to global ranking.

First Introduced In

SQL:2003 standard

Frequently Asked Questions

What is the difference between RANK and ROW_NUMBER?

ROW_NUMBER assigns unique sequential numbers even when rows tie; RANK assigns the same number to ties and skips the next numbers accordingly.

Can I use RANK in a WHERE clause?

You cannot reference RANK directly in the same SELECT level's WHERE clause. Use a subquery or CTE to compute the rank first, then filter on it.

How are NULLs handled in RANK?

Ordering of NULLs follows the database's default or explicit NULLS FIRST/NULLS LAST specification. Ties involving NULLs receive the same rank.

Is RANK faster than using correlated subqueries for ranking?

In modern databases, window functions like RANK are optimized and usually outperform equivalent correlated subqueries for large datasets.