Back to list Standard SQL What’s the Difference Between RANK and DENSE_RANK in SQL? Database: SQL PostgreSQL Oracle SQL Server MySQL SQLite Operators:RANK, DENSE_RANK , OVER, PARTITION BY, ORDER BY, ASC, DESC Problem: You want to compare the rankings produced by RANK and DENSE_RANK and add them as new columns to a table. Example: Our database has a table named sales_assistant with data in the following columns: id (primary key), first_name, last_name, month, and sold products. idfirst_namelast_namemonthsold products 1LisaBlack 52300 2MaryJacobs52400 3LisaBlack 62700 4MaryJacobs62700 5AlexSmith 62900 6MaryJacobs71200 7LisaBlack 71200 8AlexSmith 71000 Let’s display each sales assistant’s first and last name and number of sold products. We also want to rank them in terms of the number of products sold in descending order. Solution 1: SELECT RANK() OVER(ORDER BY sold products DESC) AS r, DENSE_RANK() OVER(ORDER BY sold products DESC) AS dr, first_name, last_name, month, sold products FROM sales_assistant; This query returns two rankings: one produced by RANK and another by DENSE_RANK. What’s the difference? Simply put, RANK skips the number of positions after records with the same rank number. The ranking RANK_DENSE returns position numbers from 1 to 6 because it doesn’t skip records with the same rank number: rdrfirst_namelast_namemonthsold products 11AlexSmith 62900 22LisaBlack 62700 22MaryJacobs62700 43MaryJacobs52400 54LisaBlack 52300 65MaryJacobs71200 65LisaBlack 71200 86AlexSmith 71000 Discussion: If you’d like to rank rows in the result set, SQL offers the RANK() and DENSE_RANK functions. These functions are used in SELECT with others columns. After RANK or DENSE_RANK, we call the OVER() function, which takes an ORDER BY clause with the name of the column to sort before assigning a ranking. Unlike DENSE_RANK, RANK skips positions after equal rankings. The number of positions skipped depends on how many rows had an identical ranking. For example, Mary and Lisa sold the same number of products and are both ranked as #2. With RANK, the next position is #4; with DENSE_RANK, the next position is #3. Both RANK and RANK_DENSE work on partitions of data: Solution 1: SELECT RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS r, DENSE_RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS dr, first_name, last_name, month, sold products FROM sales_assistant; You can split records into groups according to a given column (in our example, month). In this situation, records are ranked as part of a partition: rdrfirst_namelast_namemonthsold products 11MaryJacobs52400 22LisaBlack 52300 11AlexSmith 62900 22LisaBlack 62700 22MaryJacobs62700 11MaryJacobs71200 11LisaBlack 71200 32AlexSmith 71000 Recommended courses: Window Functions Window Functions in SQL Server Recommended articles: How to Use Rank Functions in SQL SQL Window Functions By Explanation See also: How to Add Ranking Positions of Rows in SQL with RANK() How to Eliminate Duplicate Rows in SQL How to Find Duplicate Rows in SQL? Tags: SQL PostgreSQL Oracle SQL Server MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.