Difference between Rank and Dense Rank in SQL

RANK() and DENSE_RANK() are SQL window functions used to assign rankings based on the values in a result set. Both assign the same rank to rows with identical values, but they differ in how they handle the next rank. RANK() skips rank numbers after a tie, while DENSE_RANK() continues with the next consecutive rank without leaving gaps.

In this blog, we’ll demystify the differences between RANK and DENSE RANK using real-world examples, practical SQL syntax, performance tips, and use cases to help you decide which one to use and when.

Overcome the challenges of NoSQL with a strong foundation in data science and machine learning. Explore our Data Science and Machine Learning Courses to master database management and advanced data techniques today!

Rank vs Dense Rank: Key Differences Between Rank and Dense Rank

Parameter	RANK()	DENSE_RANK()
Ranking Logic	Assigns the same rank to ties, then skips the next rank(s)	Assigns the same rank to ties, no ranks are skipped
Gaps in Ranking	Yes – introduces gaps after duplicates	No – ranks remain consecutive even with ties
Use Case	Best for ranking where position matters (e.g., leaderboard)	Best for reports with strict rank order continuity
Output Behavior (with Ties)	Ties get same rank, next rank jumps ahead	Ties get same rank, next rank is just one higher
Syntax	RANK() OVER (PARTITION BY ... ORDER BY ...)	DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)
Performance	Nearly identical performance	Nearly identical performance
First Rank Assigned	Always starts from 1	Always starts from 1
Result Consistency	Varies when there are ties	More consistent in sequential ranking
Gap Sensitivity	Sensitive to duplicates – causes rank jumps	Ignores gaps caused by duplicates
Sorting Requirement	Requires ORDER BY in OVER() clause	Requires ORDER BY in OVER() clause
Standard Compliance	Part of SQL:2003 standard	Part of SQL:2003 standard
Ideal For	Competitive rankings, top-N with tie handling	Business reports, non-skip rank listings, product/category sorting

Also Read:

• Difference Between SQL and PL-SQL
• Difference Between DeepSeek and ChatGPT
• Difference Between Bias and Variance

SQL Query Examples: Rank vs Dense Rank in Action

Let’s take a simple dataset called sales_data:

Employee_ID	Employee_Name	Revenue
101	Alice	1000
102	Bob	900
103	Charlie	900
104	David	800
105	Eva	700

Using RANK():

SELECT Employee_Name, Revenue,

       RANK() OVER (ORDER BY Revenue DESC) AS rank_position

FROM sales_data;

Output:

Employee_Name	Revenue	rank_position
Alice	1000	1
Bob	900	2
Charlie	900	2
David	800	4
Eva	700	5

Note: Rank 3 is skipped due to the tie between Bob and Charlie.

Using DENSE_RANK():

SELECT Employee_Name, Revenue,

       DENSE_RANK() OVER (ORDER BY Revenue DESC) AS dense_rank_position

FROM sales_data;

Output:

Employee_Name	Revenue	dense_rank_position
Alice	1000	1
Bob	900	2
Charlie	900	2
David	800	3
Eva	700	4

Note: No ranks are skipped—ranking remains dense.

Must Read:

• Difference Between Pandas and NumPy
• Difference Between Classification and Clustering

What is RANK() in SQL?

RANK() is a window function in SQL used to assign a unique rank to rows within a result set based on the specified order of values. It is typically used with the OVER() clause and ORDER BY to generate rankings within partitions.

However, if two or more rows have the same value, RANK() assigns them the same rank—but skips the next rank(s), creating gaps.

Take your data science skills to the next level with these top courses:

• Master of Science in AI & Data Science – Jindal Global University
• PG Diploma in Data Science – IIIT Bangalore
• The U & AI GenAI Certificate Program – Microsoft

Syntax of Rank Function in SQL:

SELECT column1, column2,

       RANK() OVER (PARTITION BY columnX ORDER BY columnY DESC) AS rank

FROM your_table;

Example:

Consider a sales table:

Employee	Revenue
Alice	1000
Bob	900
Charlie	900
Dave	800

Using RANK() on Revenue:

Employee	Revenue	Rank
Alice	1000	1
Bob	900	2
Charlie	900	2
Dave	800	4

Note: Rank 3 is skipped due to tie.

Use Cases:

• Leaderboards with positional gaps (e.g., sports rankings)
• Top-N reports with accurate positional ranking
• Handling datasets with tied scores or values

Also Read:

• Difference Between Deep Learning and NLP
• Difference Between IOT and AI

What is DENSE_RANK() in SQL?

DENSE_RANK() is a window function in SQL that assigns ranks to rows based on a specified order—but unlike RANK(), it does not skip ranks when duplicate values occur. This ensures consecutive ranking even when there are ties.

It’s especially useful when you want no gaps in the ranked output.

Syntax of Dense Rank in SQL:

SELECT column1, column2,

       DENSE_RANK() OVER (PARTITION BY columnX ORDER BY columnY DESC) AS dense_rank

FROM your_table;

Example:

Using the same sales table:

Employee	Revenue
Alice	1000
Bob	900
Charlie	900
Dave	800

With DENSE_RANK() on Revenue:

Employee	Revenue	Dense_Rank
Alice	1000	1
Bob	900	2
Charlie	900	2
Dave	800	3

Note: No skipped ranks—ranks are dense and consecutive.

Must Read:

• Difference Between Random Forest and Decision Tree
• Difference Between Bagging and Boosting

Key Difference from RANK():

While both functions assign the same rank to tied values, DENSE_RANK() does not leave gaps in the sequence—unlike RANK().

RANK() and DENSE_RANK(): When to Use 

Choosing between RANK() and DENSE_RANK() depends on how you want to handle ties in your result set and the business context of your report.

Use RANK() When:

• Positional accuracy matters, including gaps in rankings.
• You’re building competitive leaderboards (e.g., sports, sales contests).
• You want to reflect real-world placement, where tied scores lead to skipped positions.
• Example: In a marathon result table, if two runners tie for 2nd place, the next runner should be 4th—not 3rd.

Use DENSE_RANK() When:

• You need consecutive ranking, even with tied values.
• You’re generating category-wise reports or product rankings with no skipped positions.
• Business needs demand a uniform ranking structure for downstream calculations or visualizations.
• Example: In a product popularity chart, if two products share the same rank, the next should be just one step below.

In short, choose RANK() for accuracy with position gaps, and DENSE_RANK() for sequential clarity without gaps.

Must Check:

• Difference Between Machine Learning and Data Analytics
• Difference Between CNN and RNN

Common Mistakes and How to Avoid Them

Even experienced developers can trip up when working with RANK() and DENSE_RANK(). Here are some frequent errors:

Mistake	Why It Happens	How to Fix It
Missing ORDER BY in OVER() clause	Without ORDER BY, ranking logic is undefined	Always specify ORDER BY inside the OVER() clause
Assuming ranks are unique	Ties result in duplicate ranks	Use ROW_NUMBER() if you need unique values
Not using PARTITION BY when needed	Ranks calculated over full dataset instead of group	Add PARTITION BY to segment results (e.g., by department)
Expecting continuous ranking from RANK()	RANK() introduces gaps	Use DENSE_RANK() for continuous ranking
Wrong column in ORDER BY	Leads to incorrect rank calculations	Double-check sorting logic based on requirement
Using RANK in WHERE clause directly	Window functions can't be used in WHERE	Use subqueries or CTEs to filter on ranks

Performance Considerations for Rank Functions

While RANK() and DENSE_RANK() are powerful, their performance can degrade with large datasets if not optimized properly.

Aspect	Insight	Optimization Tip
Execution Time	Both functions use sorting; expensive on large datasets	Create indexes on columns used in ORDER BY
Memory Usage	Sorting over wide partitions consumes memory	Minimize partition size when possible
Parallelism	Modern SQL engines parallelize window functions	Use efficient query plans and review EXPLAIN plans
Indexing	No direct index usage for window functions	But indexes on ORDER BY columns can improve sort performance
CTE/Subquery Overuse	Over-nesting leads to redundant computation	Materialize intermediate results or use temp tables for reuse
Filtering Ranked Rows	Filtering ranks requires another layer (CTE or subquery)	Use WITH clause and filter in outer query for clarity and speed

Conclusion: Which One Should You Use?

Both RANK() and DENSE_RANK() are powerful tools for ranking data, but the choice depends on the context of your analysis.

• Choose RANK() when gaps in ranking matter—such as leaderboards, competitions, or use cases where positional accuracy reflects real-world hierarchy.
• Opt for DENSE_RANK() when you need continuous, gap-free rankings—like reporting, product listings, or dashboards where visual clarity is key.

Similar Read: Check our Top Differences Between Blogs

• Difference Between Blockchain and Big Data
• Difference Between SQL and MySQL
• Difference Between Linear Regression and Logistic Regression
• Difference Between Correlation and Regression
• Difference Between DBMS and RDBMS
• Difference Between Data Science and Artificial Intelligence
• Difference Between Big Data and Data Analytics