Design a query to simulate the RANK() function without using window functions.

Instruction: Write a SQL query that assigns a rank to each row within a partition of a result set, without utilizing any SQL window functions.

Context: This question assesses the candidate's ability to understand and manipulate data sets in complex ways, mimicking the behavior of advanced SQL functions through creative query writing.

Updated

Example Answer

To reproduce RANK over salary descending, I would count rows with a strictly higher salary and add one. Equal salaries receive the same rank, and ties leave gaps. For salaries 100, 100, and 90, the ranks are 1, 1, and 3. That is different from DENSE_RANK, which would give 1, 1, and 2. I would state a null policy first; this example assumes salary is non-null. On a large table, I would prefer the window function when available and compare query plans.

Example

SELECT e.id, e.salary,
       1 + (SELECT COUNT(*) FROM employees AS higher
            WHERE higher.salary > e.salary) AS salary_rank
FROM employees AS e
ORDER BY e.salary DESC, e.id;

The outer id orders the displayed ties; it is deliberately absent from the rank calculation. Adding id to the comparison would turn peer salaries into distinct positions. Test duplicate salaries, a single row, and an empty table. The correlated count can be expensive, even with an index.

Practice follow-up

How would the calculation change for DENSE_RANK or ranks within each department?

Sources

PostgreSQL window function semantics.

Related Questions