How to Use RANK in PostgreSQL

Key Insights

RANK() assigns the same rank to tied values and skips subsequent ranks, while DENSE_RANK() doesn’t skip ranks and ROW_NUMBER() assigns unique numbers regardless of ties—choose based on whether gaps in your ranking sequence matter for your use case.
Always use PARTITION BY when you need separate ranking sequences for different groups (like ranking sales by region or products by category), as it resets the rank counter for each partition without requiring separate queries.
Wrap RANK() queries in CTEs or subqueries when filtering by rank values (like “top 3 per group”), since you cannot use window functions directly in WHERE clauses due to SQL’s order of operations.

Understanding Window Functions and RANK

PostgreSQL’s window functions operate on a set of rows related to the current row, without collapsing them into a single output like aggregate functions do. RANK() is one of the most commonly used window functions for assigning sequential positions to rows based on specified criteria.

The critical difference between the three ranking functions lies in how they handle ties:

WITH sample_data AS (
  SELECT * FROM (VALUES
    ('Alice', 95),
    ('Bob', 87),
    ('Charlie', 87),
    ('Diana', 82),
    ('Eve', 82),
    ('Frank', 78)
  ) AS t(name, score)
)
SELECT 
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank,
  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num
FROM sample_data;

Results:

name    | score | rank | dense_rank | row_num
--------|-------|------|------------|--------
Alice   | 95    | 1    | 1          | 1
Bob     | 87    | 2    | 2          | 2
Charlie | 87    | 2    | 2          | 3
Diana   | 82    | 4    | 3          | 4
Eve     | 82    | 4    | 3          | 5
Frank   | 78    | 6    | 4          | 6

Notice how RANK() jumps from 2 to 4, skipping 3 because two people tied for second place. DENSE_RANK() continues sequentially without gaps. ROW_NUMBER() assigns unique values even to ties, making the order somewhat arbitrary for tied values.

Basic RANK Syntax and Usage

The fundamental syntax requires an ORDER BY clause within the OVER() specification. Without ORDER BY, RANK() is meaningless—you must define what you’re ranking by.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary DECIMAL(10,2)
);

INSERT INTO employees (name, department, salary) VALUES
  ('John Smith', 'Engineering', 95000),
  ('Jane Doe', 'Engineering', 105000),
  ('Mike Johnson', 'Engineering', 95000),
  ('Sarah Williams', 'Sales', 75000),
  ('Tom Brown', 'Sales', 82000);

SELECT 
  name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

This produces a company-wide salary ranking. John Smith and Mike Johnson both earn $95,000, so they share rank 3, and the next person gets rank 5. This gap behavior is crucial: RANK() tells you “how many people scored better than this person” plus one. When two people tie for third, they both have exactly two people who scored better, so they’re both rank 3.

PARTITION BY: Ranking Within Groups

The real power of RANK() emerges when you use PARTITION BY to create independent ranking sequences for different groups. This is essential for answering questions like “Who are the top performers in each department?” without writing separate queries.

SELECT 
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as dept_rank
FROM employees
ORDER BY department, dept_rank;

Results:

name           | department   | salary  | dept_rank
---------------|--------------|---------|----------
Jane Doe       | Engineering  | 105000  | 1
John Smith     | Engineering  | 95000   | 2
Mike Johnson   | Engineering  | 95000   | 2
Tom Brown      | Sales        | 82000   | 1
Sarah Williams | Sales        | 75000   | 2

The ranking resets for each department. Jane Doe ranks #1 in Engineering, and Tom Brown ranks #1 in Sales—completely independent sequences.

Here’s a more complex example with product sales by category:

CREATE TABLE product_sales (
  product_name VARCHAR(100),
  category VARCHAR(50),
  monthly_revenue DECIMAL(10,2),
  sale_month DATE
);

INSERT INTO product_sales VALUES
  ('Laptop Pro', 'Electronics', 45000, '2024-01-01'),
  ('Wireless Mouse', 'Electronics', 8000, '2024-01-01'),
  ('Office Chair', 'Furniture', 12000, '2024-01-01'),
  ('Standing Desk', 'Furniture', 18000, '2024-01-01'),
  ('Desk Lamp', 'Furniture', 3000, '2024-01-01');

SELECT 
  category,
  product_name,
  monthly_revenue,
  RANK() OVER (
    PARTITION BY category 
    ORDER BY monthly_revenue DESC
  ) as category_rank
FROM product_sales
WHERE sale_month = '2024-01-01';

Practical Use Cases

Finding Top N Per Group

One of the most common patterns is finding the top performers in each group. You must use a CTE or subquery because window functions cannot appear in WHERE clauses:

WITH ranked_sales AS (
  SELECT 
    region,
    salesperson,
    total_sales,
    RANK() OVER (
      PARTITION BY region 
      ORDER BY total_sales DESC
    ) as sales_rank
  FROM regional_sales
)
SELECT region, salesperson, total_sales
FROM ranked_sales
WHERE sales_rank <= 3
ORDER BY region, sales_rank;

This pattern is incredibly efficient compared to alternatives like correlated subqueries or self-joins for each region.

Monthly Leaderboards with Trends

WITH monthly_rankings AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    customer_id,
    SUM(order_total) as monthly_spend,
    RANK() OVER (
      PARTITION BY DATE_TRUNC('month', order_date)
      ORDER BY SUM(order_total) DESC
    ) as monthly_rank
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY DATE_TRUNC('month', order_date), customer_id
)
SELECT 
  month,
  customer_id,
  monthly_spend,
  monthly_rank,
  LAG(monthly_rank) OVER (
    PARTITION BY customer_id 
    ORDER BY month
  ) as previous_month_rank
FROM monthly_rankings
WHERE monthly_rank <= 10;

This query shows your top 10 customers each month and tracks how their ranking changed from the previous month.

Advanced Patterns and Performance Considerations

Filtering with CTEs

When you need to filter by rank, always use CTEs or subqueries. This won’t work:

-- WRONG: This will error
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
WHERE rnk <= 5;

Instead:

-- CORRECT
WITH ranked_employees AS (
  SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT name, salary, rnk
FROM ranked_employees
WHERE rnk <= 5;

Performance and Indexing

Window functions require sorting unless the data is already ordered appropriately. Create indexes on your ORDER BY and PARTITION BY columns:

-- For queries that rank by salary within departments
CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);

Check your query plans:

EXPLAIN ANALYZE
WITH ranked AS (
  SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;

Look for “WindowAgg” nodes in the plan. If you see sequential scans on large tables, your indexes aren’t being used effectively.

Common Pitfalls and Troubleshooting

NULL Handling

NULLs sort last by default in PostgreSQL (with ASC) or first (with DESC). Be explicit about NULL handling:

-- NULLs will rank first with DESC
SELECT 
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM test_scores;

-- Force NULLs to rank last
SELECT 
  name,
  score,
  RANK() OVER (ORDER BY score DESC NULLS LAST) as rank
FROM test_scores;

Forgetting ORDER BY

This is meaningless and will likely give unexpected results:

-- WRONG: No ORDER BY
SELECT name, RANK() OVER (PARTITION BY department) as rnk
FROM employees;

Every row in each partition gets rank 1 because there’s no ordering criterion.

Misunderstanding Tie Behavior

If you need consecutive ranks without gaps, use DENSE_RANK(). If you need to break ties arbitrarily, use ROW_NUMBER() with additional ordering columns:

-- Break salary ties by employee ID
SELECT 
  name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC, id) as unique_rank
FROM employees;

RANK() is your go-to function for most ranking scenarios in PostgreSQL. Use it with PARTITION BY for group-wise rankings, wrap it in CTEs when filtering by rank, and index your partition and order columns for optimal performance. Understanding how it handles ties compared to DENSE_RANK() and ROW_NUMBER() ensures you choose the right tool for your specific use case.