SQL window functions are useful when working with employee rankings, sales reports and business data.

Three commonly used ranking functions are "ROW_NUMBER()", "RANK()" and "DENSE_RANK()". Although they look similar, they produce different results when multiple records have the same value.
If you want to understand SQL window functions in more detail, including "PARTITION BY", "LAG()", "LEAD()", running totals and interview questions, you can also explore this "SQL Window Functions and Analytic Functions Guide" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/window-functions-analytic-functions-sql-faq.html).
The Problem
Suppose you have an employees table containing employee IDs, employee names, departments and salaries.
emp_id| emp_name| department| salary
101| Amit| IT| 90000
102| Rahul| IT| 80000
103| Priya| HR| 80000
104| Neha| HR| 70000
105| Karan| IT| 80000
106| Sneha| HR| 65000
You need to compare the results of three SQL ranking functions when employees have equal salaries.
SQL Solution (Oracle)
SELECT
emp_id,
emp_name,
department,
salary,
ROW_NUMBER() OVER (
ORDER BY salary DESC, emp_id
) AS row_num,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank,
DENSE_RANK() OVER (
ORDER BY salary DESC
) AS dense_rank
FROM employees
ORDER BY salary DESC, emp_id;
Expected Output
emp_id| emp_name| salary| row_num| salary_rank| dense_rank
101| Amit| 90000| 1| 1| 1
102| Rahul| 80000| 2| 2| 2
103| Priya| 80000| 3| 2| 2
105| Karan| 80000| 4| 2| 2
104| Neha| 70000| 5| 5| 3
106| Sneha| 65000| 6| 6| 4
Understanding the Difference
- "ROW_NUMBER()" assigns a unique sequential number to every row.
- "RANK()" assigns the same rank to equal salaries and skips subsequent rank numbers.
- "DENSE_RANK()" assigns the same rank to equal salaries without skipping rank numbers.
Your Turn
Suppose you want to find the top three distinct salary levels in each department, including all employees who share the same salary.
Which SQL ranking function would you use?
Share your answer in the comments!
For more practical SQL examples and interview preparation, explore the SQL window functions guide linked above.
Top comments (0)