intro
Master the RANK window function in SQL with practical examples. Learn syntax, performance tips, and real-world applications across all major databases.
The RANK window function stands out as one of the most powerful tools for creating ordered result sets with ranking logic in SQL. Understanding how to leverage the RANK function in SQL helps you convert complex queries into elegant solutions.
This guide will teach you everything you need to know about the RANK window analytical function, from basic syntax to advanced real-world applications. By the end of this article, you'll be equipped to implement sophisticated ranking logic in your SQL queries with confidence!
What Is the RANK Window Function?
RANK is window analytical function that assigns a rank to each row within a partition of a result set. Unlike traditional GROUP BY operations that collapse rows, window functions preserve individual rows while performing calculations across a set of rows related to the current row.
Take a look at some of the RANK window function in SQL:
RANK Window Function: Basic Syntax
The syntax of the RANK function in standard ANSI SQL is:
1
RANK() OVER (
2
[PARTITION BY partition_expression]
3
ORDER BY order_expression [ASC|DESC]
4
)
Where:
RANK Function Examples in SQL
See the RANK function in action in some real-world scenarios.
Note: All queries in this section are going to be run in DbVisualizer—the database client with the highest user satisfaction in the market. Any other database client will do.
Example #1 — Ranking Products by Price
Let’s look at this basic example:
1
-- Create sample products table
2
CREATE TABLE products (
3
product_id INT PRIMARY KEY,
4
product_name VARCHAR(100),
5
category VARCHAR(50),
6
price DECIMAL(10,2),
7
units_sold INT
8
);
9
10
-- Populating sample table
11
INSERT INTO products VALUES
12
(1, 'Laptop Pro', 'Electronics', 1299.99, 150),
13
(2, 'Wireless Mouse', 'Electronics', 29.99, 500),
14
(3, 'USB-C Hub', 'Electronics', 49.99, 300),
15
(4, 'Office Chair', 'Furniture', 299.99, 75),
16
(5, 'Standing Desk', 'Furniture', 599.99, 50),
17
(6, 'Desk Lamp', 'Furniture', 39.99, 200);
18
19
-- Rank products by price (highest to lowest)
20
SELECT
21
product_name,
22
category,
23
price,
24
RANK() OVER (ORDER BY price DESC) as price_rank
25
FROM products
26
ORDER BY price_rank;
Explanation:
Try this variation to see how RANK handles ties:
1
-- Update some prices to create ties
2
UPDATE products SET price = 299.99 WHERE product_id IN (4, 3);
3
4
-- Re-run the ranking query
5
SELECT
6
product_name,
7
category,
8
price,
9
RANK() OVER (ORDER BY price DESC) as price_rank
10
FROM products
11
ORDER BY price_rank;
Now ‘USB-C Hub’ and ‘Office Chair’ will both have rank 3, and the next item will have rank 5 (skipping 4).
Example #2 — Employee Performance Ranking System
Assume you want to build an employee performance ranking system that evaluates employees across multiple dimensions and creates both company-wide and department-specific rankings.
We are analyzing Q1-2024 performance data for employees across three departments (Sales, Marketing, IT) using three key metrics: sales amount, customer satisfaction, and projects completed.
1
-- Create employee performance table
2
CREATE TABLE employee_performance (
3
employee_id INT PRIMARY KEY,
4
employee_name VARCHAR(100),
5
department VARCHAR(50),
6
quarter VARCHAR(10),
7
sales_amount DECIMAL(12,2),
8
customer_satisfaction DECIMAL(3,2),
9
projects_completed INT
10
);
11
12
-- Insert sample data
13
INSERT INTO employee_performance VALUES
14
(1, 'Sarah Johnson', 'Sales', 'Q1-2024', 125000.00, 4.8, 15),
15
(2, 'Mike Chen', 'Sales', 'Q1-2024', 118000.00, 4.9, 12),
16
(3, 'Lisa Williams', 'Sales', 'Q1-2024', 125000.00, 4.7, 14),
17
(4, 'James Brown', 'Marketing', 'Q1-2024', 95000.00, 4.6, 20),
18
(5, 'Emma Davis', 'Marketing', 'Q1-2024', 102000.00, 4.8, 18),
19
(6, 'Robert Miller', 'IT', 'Q1-2024', 110000.00, 4.5, 25),
20
(7, 'Anna Wilson', 'IT', 'Q1-2024', 108000.00, 4.9, 22);
21
22
-- Comprehensive performance ranking
23
WITH performance_metrics AS (
24
SELECT
25
employee_id,
26
employee_name,
27
department,
28
quarter,
29
sales_amount,
30
customer_satisfaction,
31
projects_completed,
32
-- Calculate composite score
33
(sales_amount / 1000) * 0.4 +
34
(customer_satisfaction * 20) * 0.3 +
35
(projects_completed * 2) * 0.3 as composite_score
36
FROM employee_performance
37
)
38
SELECT
39
employee_name,
40
department,
41
ROUND(composite_score, 2) as performance_score,
42
RANK() OVER (ORDER BY composite_score DESC) as company_rank,
43
RANK() OVER (PARTITION BY department ORDER BY composite_score DESC) as dept_rank,
44
CASE
45
WHEN RANK() OVER (PARTITION BY department ORDER BY composite_score DESC) = 1
46
THEN 'Top Performer'
47
WHEN RANK() OVER (PARTITION BY department ORDER BY composite_score DESC) <= 3
48
THEN 'High Performer'
49
ELSE 'Standard Performer'
50
END as performance_tier
51
FROM performance_metrics
52
ORDER BY company_rank;
The composite score formula balances three performance metrics:
1
Composite Score = (Sales × 0.4) + (Satisfaction × 0.3) + (Projects × 0.3)
Normalization applied:
For example, the calculations for Sarah Johnson are:
1
Sales component: 125,000 / 1000 × 0.4 = 50.00
2
Satisfaction: 4.8 × 20 × 0.3 = 28.80
3
Projects: 15 × 2 × 0.3 = 9.00
4
Total Score: 50.00 + 28.80 + 9.00 = 87.80
Given that, the query output in DbVisualizer will be:
It can be seen that:
Example #3 — E-commerce Product Analysis
Consider the scenario where you are analyzing the January 2024 sales data for products across three categories (Electronics, Sports, Home) to understand which products excel in revenue, profit, volume, and overall performance.
1
-- Create e-commerce sales table
2
CREATE TABLE ecommerce_sales (
3
sale_id INT PRIMARY KEY,
4
product_id INT,
5
product_name VARCHAR(100),
6
category VARCHAR(50),
7
subcategory VARCHAR(50),
8
sale_date DATE,
9
quantity INT,
10
revenue DECIMAL(10,2),
11
profit_margin DECIMAL(5,2)
12
);
13
14
-- Populating sample table(representing monthly aggregated data)
15
INSERT INTO ecommerce_sales VALUES
16
(1, 101, 'Smartphone X', 'Electronics', 'Mobile', '2024-01-31', 250, 125000.00, 0.25),
17
(2, 102, 'Laptop Pro 15', 'Electronics', 'Computers', '2024-01-31', 80, 96000.00, 0.20),
18
(3, 103, 'Wireless Earbuds', 'Electronics', 'Audio', '2024-01-31', 500, 40000.00, 0.35),
19
(4, 104, 'Running Shoes', 'Sports', 'Footwear', '2024-01-31', 300, 24000.00, 0.40),
20
(5, 105, 'Yoga Mat', 'Sports', 'Fitness', '2024-01-31', 450, 13500.00, 0.45),
21
(6, 106, 'Coffee Maker', 'Home', 'Kitchen', '2024-01-31', 120, 18000.00, 0.30),
22
(7, 107, 'Blender Pro', 'Home', 'Kitchen', '2024-01-31', 150, 15000.00, 0.32);
23
24
-- Multi-dimensional product ranking analysis
25
WITH product_metrics AS (
26
SELECT
27
product_id,
28
product_name,
29
category,
30
subcategory,
31
quantity,
32
revenue,
33
profit_margin,
34
revenue * profit_margin as profit,
35
revenue / quantity as avg_price
36
FROM ecommerce_sales
37
),
38
ranked_products AS (
39
SELECT
40
product_name,
41
category,
42
subcategory,
43
revenue,
44
profit,
45
quantity,
46
RANK() OVER (ORDER BY revenue DESC) as revenue_rank,
47
RANK() OVER (ORDER BY profit DESC) as profit_rank,
48
RANK() OVER (ORDER BY quantity DESC) as volume_rank,
49
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as category_revenue_rank
50
FROM product_metrics
51
)
52
SELECT
53
product_name,
54
category,
55
subcategory,
56
revenue,
57
revenue_rank,
58
profit_rank,
59
volume_rank,
60
category_revenue_rank,
61
DENSE_RANK() OVER (
62
ORDER BY (revenue_rank + profit_rank + volume_rank) / 3.0
63
) as combined_rank
64
FROM ranked_products
65
ORDER BY combined_rank;
Here, the query:
Great! It is very clear in the DbVisualizer output above—‘Smartphone X’ tops the combined rank followed by ‘Wireless Earbuds’, etc.
Example #4 — Partitioned Rankings
One of the most powerful features of the RANK window function is the ability to create separate rankings within groups using PARTITION BY. Take a look at this use case:
1
-- Rank products within each category
2
SELECT
3
product_name,
4
category,
5
price,
6
RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_price_rank,
7
RANK() OVER (ORDER BY price DESC) as overall_price_rank
8
FROM products
9
ORDER BY category, category_price_rank;
Explanation:
This example is perfect business use case for finding the top N products in each category for promotional displays or inventory analysis.
RANK vs ROW_NUMBER vs DENSE_RANK in SQL
These three ranking functions are often confused, but each serves a distinct purpose in SQL analytics. Understanding their differences is crucial for choosing the right function for your specific ranking requirements.
The main distinction lies in how they handle tied values:
See these differences of the three window functions with a practical example:
1
WITH sales_data AS (
2
SELECT * FROM (VALUES
3
('Julian', 1500),
4
('Bobby', 1500),
5
('MikeJay', 1000),
6
('Adelaide', 900),
7
('Michael', 1000),
8
('Oubby', 1200)
9
) AS t(salesperson, amount)
10
)
11
SELECT
12
salesperson,
13
amount,
14
RANK() OVER (ORDER BY amount DESC) as rank_result,
15
DENSE_RANK() OVER (ORDER BY amount DESC) as dense_rank_result,
16
ROW_NUMBER() OVER (ORDER BY amount DESC) as row_number_result
17
FROM sales_data
18
ORDER BY amount DESC, salesperson;
Observations:
When to Use Each
Best Practices for Working with The RANK Window Function
Below are some of the best practices that should be taken into consideration when working with the RANK Window Function in SQL:
Practice 1: When using RANK() functions, ensure proper indexing on columns used in ORDER BY and PARTITION BY clauses:
1
-- Create indexes for better performance
2
CREATE INDEX idx_products_category_price ON products(category, price DESC);
3
CREATE INDEX idx_employee_dept_composite ON employee_performance(department, quarter);
Practice 2: Apply WHERE clauses before window functions to reduce the dataset like this:
1
-- Filter first, then rank
2
WITH filtered_products AS (
3
SELECT * FROM products
4
WHERE category IN ('Electronics', 'Furniture')
5
AND units_sold > 50
6
)
7
SELECT
8
product_name,
9
category,
10
RANK() OVER (PARTITION BY category ORDER BY price DESC) as filtered_rank
11
FROM filtered_products;
Practice 3: Always use appropriate window specifications to avoid unnecessary calculations:
1
-- Efficient: Single pass for multiple rankings
2
SELECT
3
product_name,
4
RANK() OVER w as price_rank,
5
DENSE_RANK() OVER w as dense_price_rank,
6
PERCENT_RANK() OVER w as price_percentile
7
FROM products
8
WINDOW w AS (ORDER BY price DESC);
That’s a wrap!
Conclusion
The RANK window function opens up a world of possibilities for data analysis in SQL. As shown here, choose RANK when gaps in ranking sequences are acceptable, DENSE_RANK when you need consecutive numbers, and ROW_NUMBER when unique identifiers are required.
Ready to put these concepts into practice? Fire up DbVisualizer and start experimenting with the examples provided. Your data is waiting to be ranked, analyzed, and transformed into actionable insights.
Happy querying!
FAQ
What is the RANK window function in SQL?
The RANK window function is an analytical function that assigns ranking numbers to rows in a result set based on the values in one or more columns. It preserves all rows while calculating rankings, assigns the same rank to identical values (ties), and creates gaps in the sequence after ties. For example, if two rows tie for rank 2, the next rank will be 4, not 3.
What is the basic syntax for using RANK window function?
The basic syntax is:
1
RANK() OVER (ORDER BY column_name [ASC|DESC])
For partitioned rankings, use:
1
RANK() OVER (PARTITION BY partition_column ORDER BY sort_column DESC)
The ORDER BY clause is mandatory and determines how rows are ranked, while PARTITION BY is optional and creates separate ranking groups.
Can I use RANK without PARTITION BY clause?
Yes, RANK can be used without PARTITION BY. When omitted, the ranking is performed across the entire result set as a single group. Adding PARTITION BY creates separate ranking sequences within each partition, which is useful for category-based rankings like "top products per department."
What are common use cases for the RANK window function in SQL?
Common applications include employee performance rankings, product sales analysis, student grade rankings, competitive leaderboards, top-N queries per category, percentile calculations, and identifying best/worst performers.
