SQL intermediate ranking employees salary dense_rank

How to Rank Employees by Salary in SQL?

Rank Employees by Salary is a SQL query that this sql query ranks employees by their salary within each department, using rank for standard ranking and dense_rank for handling ties.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Discover how to rank employees within their departments based on salary, while properly managing ties using RANK and DENSE_RANK functions.

Generate This Formula Browse Library

The Formula

Prompt

"Rank employees by salary within each department, handling ties with RANK and DENSE_RANK"

SQL

SELECT department, employee_name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary_rank FROM employees;

This SQL query ranks employees by their salary within each department, using RANK for standard ranking and DENSE_RANK for handling ties.

Step-by-Step Breakdown

Use the RANK() function to assign a rank to each employee based on their salary within each department.
Use the DENSE_RANK() function to assign a rank without gaps for tied salaries.
Partition the results by department to ensure rankings are calculated separately for each department.
Order the results by salary in descending order to rank the highest salaries first.

Edge Cases & Warnings

Employees with the same salary in the same department will receive the same rank using RANK.
DENSE_RANK will assign the next rank immediately after tied ranks, unlike RANK which skips numbers.
If there are no employees in a department, the query will return no results for that department.

Examples

Prompt

"Department: Sales, Employees: Alice ($5000), Bob ($5000), Charlie ($4000)"

SQL

Alice: RANK 1, DENSE_RANK 1; Bob: RANK 1, DENSE_RANK 1; Charlie: RANK 3, DENSE_RANK 2

Prompt

"Department: Engineering, Employees: Dave ($7000), Eve ($6000), Frank ($6000)"

SQL

Dave: RANK 1, DENSE_RANK 1; Eve: RANK 2, DENSE_RANK 2; Frank: RANK 2, DENSE_RANK 2

Frequently Asked Questions

What is the difference between RANK and DENSE_RANK?

RANK assigns the same rank to tied values but skips subsequent ranks, while DENSE_RANK assigns the next rank without gaps.

Can I use RANK and DENSE_RANK together?

Yes, you can use both in a query to compare how they handle ties.

What happens if there are no employees in a department?

The query will return no results for that department.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples