How to Use DENSE_RANK in PostgreSQL
DENSE_RANK is a window function in PostgreSQL that assigns a rank to each row within a result set, with no gaps in the ranking sequence when ties occur. This distinguishes it from both RANK and...
Key Insights
- DENSE_RANK assigns consecutive ranks without gaps, making it ideal for leaderboards and top-N queries where tied values should share the same rank
- Combining DENSE_RANK with PARTITION BY enables independent ranking within groups, essential for category-based comparisons and segmented analytics
- Always filter DENSE_RANK results in outer queries or CTEs, never in the WHERE clause of the window function itself, to avoid syntax errors and ensure correct results
Introduction to DENSE_RANK
DENSE_RANK is a window function in PostgreSQL that assigns a rank to each row within a result set, with no gaps in the ranking sequence when ties occur. This distinguishes it from both RANK and ROW_NUMBER, and understanding these differences is critical for choosing the right function.
Here’s how they compare:
WITH scores AS (
SELECT * FROM (VALUES
('Alice', 95),
('Bob', 95),
('Charlie', 90),
('David', 85)
) AS t(name, score)
)
SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
RANK() OVER (ORDER BY score DESC) as rank,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM scores;
Results:
name | score | row_num | rank | dense_rank
----------+-------+---------+------+------------
Alice | 95 | 1 | 1 | 1
Bob | 95 | 2 | 1 | 1
Charlie | 90 | 3 | 3 | 2
David | 85 | 4 | 4 | 3
ROW_NUMBER assigns unique sequential numbers regardless of ties. RANK skips numbers after ties (1, 1, 3, 4). DENSE_RANK maintains consecutive numbering (1, 1, 2, 3). Use DENSE_RANK when you need consistent rank progressions, such as in competitive leaderboards or when selecting “top 3 categories” where ties shouldn’t create gaps.
Basic DENSE_RANK Syntax and Usage
The fundamental syntax requires only an ORDER BY clause within the OVER() specification:
DENSE_RANK() OVER (ORDER BY column_name [ASC|DESC])
Let’s apply this to a practical scenario with employee salaries:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC(10, 2)
);
INSERT INTO employees (name, department, salary) VALUES
('Sarah Chen', 'Engineering', 120000),
('Mike Johnson', 'Engineering', 115000),
('Emily Brown', 'Sales', 95000),
('James Wilson', 'Engineering', 115000),
('Lisa Anderson', 'Sales', 105000),
('David Lee', 'Marketing', 85000);
SELECT
name,
department,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
ORDER BY salary_rank;
Results:
name | department | salary | salary_rank
---------------+--------------+-----------+-------------
Sarah Chen | Engineering | 120000.00 | 1
Mike Johnson | Engineering | 115000.00 | 2
James Wilson | Engineering | 115000.00 | 2
Lisa Anderson | Sales | 105000.00 | 3
Emily Brown | Sales | 95000.00 | 4
David Lee | Marketing | 85000.00 | 5
Notice Mike and James share rank 2, and the next rank is 3, not 4. This consecutive numbering is DENSE_RANK’s defining characteristic.
DENSE_RANK with PARTITION BY
PARTITION BY divides your result set into groups, applying DENSE_RANK independently within each partition. This is invaluable for category-based rankings.
CREATE TABLE product_sales (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
monthly_sales NUMERIC(10, 2)
);
INSERT INTO product_sales (product_name, category, monthly_sales) VALUES
('Laptop Pro', 'Electronics', 45000),
('Wireless Mouse', 'Electronics', 12000),
('USB Cable', 'Electronics', 8000),
('Office Chair', 'Furniture', 15000),
('Standing Desk', 'Furniture', 28000),
('Desk Lamp', 'Furniture', 9000),
('Monitor', 'Electronics', 32000);
SELECT
category,
product_name,
monthly_sales,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY monthly_sales DESC
) as category_rank
FROM product_sales
ORDER BY category, category_rank;
Results:
category | product_name | monthly_sales | category_rank
-------------+----------------+---------------+---------------
Electronics | Laptop Pro | 45000.00 | 1
Electronics | Monitor | 32000.00 | 2
Electronics | Wireless Mouse | 12000.00 | 3
Electronics | USB Cable | 8000.00 | 4
Furniture | Standing Desk | 28000.00 | 1
Furniture | Office Chair | 15000.00 | 2
Furniture | Desk Lamp | 9000.00 | 3
Each category now has independent rankings starting from 1. This pattern is essential for comparative analytics across segments.
Practical Applications and Real-World Scenarios
Finding Top N in Each Group
A common requirement is identifying the top performers within each category:
CREATE TABLE sales_reps (
rep_id SERIAL PRIMARY KEY,
rep_name VARCHAR(100),
region VARCHAR(50),
quarterly_sales NUMERIC(12, 2)
);
INSERT INTO sales_reps (rep_name, region, quarterly_sales) VALUES
('John Smith', 'West', 250000),
('Jane Doe', 'West', 280000),
('Bob Wilson', 'West', 220000),
('Alice Brown', 'East', 310000),
('Charlie Davis', 'East', 290000),
('Eva Martinez', 'East', 290000),
('Frank Taylor', 'South', 195000);
WITH ranked_sales AS (
SELECT
rep_name,
region,
quarterly_sales,
DENSE_RANK() OVER (
PARTITION BY region
ORDER BY quarterly_sales DESC
) as rank
FROM sales_reps
)
SELECT *
FROM ranked_sales
WHERE rank <= 2
ORDER BY region, rank;
Results:
rep_name | region | quarterly_sales | rank
---------------+--------+-----------------+------
Alice Brown | East | 310000.00 | 1
Charlie Davis | East | 290000.00 | 2
Eva Martinez | East | 290000.00 | 2
Frank Taylor | South | 195000.00 | 1
Jane Doe | West | 280000.00 | 1
John Smith | West | 250000.00 | 2
Notice the East region returns three reps because of the tie at rank 2. With RANK, you’d get the same result, but with ROW_NUMBER, you’d arbitrarily exclude one of the tied salespeople.
Leaderboard Implementation
For gaming or competitive scenarios, DENSE_RANK handles ties elegantly:
CREATE TABLE player_scores (
player_id SERIAL PRIMARY KEY,
username VARCHAR(50),
score INTEGER,
achieved_at TIMESTAMP
);
INSERT INTO player_scores (username, score, achieved_at) VALUES
('ProGamer99', 15000, '2024-01-15 10:30:00'),
('SkillMaster', 15000, '2024-01-15 11:45:00'),
('NovicePlayer', 8500, '2024-01-15 09:20:00'),
('EliteSniper', 12000, '2024-01-15 14:10:00');
SELECT
username,
score,
DENSE_RANK() OVER (ORDER BY score DESC) as leaderboard_position,
achieved_at
FROM player_scores
ORDER BY leaderboard_position, achieved_at;
Both players with 15000 points share position 1, and the next player is position 2, not 3—exactly what users expect in a leaderboard.
DENSE_RANK in Complex Queries
Window functions shine when combined with CTEs and subqueries for multi-step analytics:
CREATE TABLE order_history (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
order_total NUMERIC(10, 2)
);
-- Find each customer's top 3 orders and calculate percentage of their total spending
WITH ranked_orders AS (
SELECT
customer_id,
order_id,
order_total,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY order_total DESC
) as order_rank
FROM order_history
),
customer_totals AS (
SELECT
customer_id,
SUM(order_total) as total_spending
FROM order_history
GROUP BY customer_id
)
SELECT
ro.customer_id,
ro.order_id,
ro.order_total,
ro.order_rank,
ROUND(100.0 * ro.order_total / ct.total_spending, 2) as pct_of_total
FROM ranked_orders ro
JOIN customer_totals ct ON ro.customer_id = ct.customer_id
WHERE ro.order_rank <= 3
ORDER BY ro.customer_id, ro.order_rank;
Critical point: Never filter window function results directly in WHERE clauses. This fails:
-- WRONG - This will error
SELECT
name,
DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) <= 3;
Always use CTEs or subqueries to filter ranked results.
Performance Considerations and Best Practices
Window functions like DENSE_RANK can be computationally expensive on large datasets. Here’s how to optimize:
Index Strategy
Create indexes on columns used in PARTITION BY and ORDER BY clauses:
-- For our sales_reps example
CREATE INDEX idx_sales_reps_region_sales
ON sales_reps(region, quarterly_sales DESC);
-- For partitioned ranking queries
CREATE INDEX idx_product_sales_category_sales
ON product_sales(category, monthly_sales DESC);
Execution Plan Analysis
Always examine query plans for large datasets:
EXPLAIN ANALYZE
SELECT
product_name,
category,
DENSE_RANK() OVER (PARTITION BY category ORDER BY monthly_sales DESC)
FROM product_sales;
Look for WindowAgg nodes in the plan. If you see sequential scans on large tables, add appropriate indexes.
When to Use Alternatives
- Use ROW_NUMBER when you need unique sequential numbers regardless of ties
- Use RANK when gaps in ranking after ties are acceptable or desired
- Use DENSE_RANK when consecutive ranks matter, especially for “top N” queries
- For simple maximum/minimum without ranking all rows, use aggregate functions with GROUP BY instead
Materialized Views for Repeated Queries
If you frequently query the same rankings, consider materialized views:
CREATE MATERIALIZED VIEW top_products_by_category AS
SELECT
category,
product_name,
monthly_sales,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY monthly_sales DESC
) as rank
FROM product_sales;
CREATE INDEX idx_top_products_rank ON top_products_by_category(category, rank);
-- Refresh periodically
REFRESH MATERIALIZED VIEW top_products_by_category;
DENSE_RANK is a powerful tool for ranking scenarios where consecutive numbering matters. Master the PARTITION BY clause for segmented rankings, always filter results in outer queries, and optimize with proper indexing for production workloads. The consecutive ranking behavior makes it ideal for leaderboards, top-N selections, and any scenario where gaps in rank numbers would confuse users or break business logic.