1. Which statement correctly contrasts ROW_NUMBER(), RANK(), and DENSE_RANK()?
Difficulty: EasyType: MCQTopic: Window Functions
ROW_NUMBER() gives a unique sequence per partition. If two rows tie on the sort key, it still assigns different numbers, so ties are broken by the engine’s tie-breakers. RANK() assigns the same rank to ties but leaves gaps after a tie. DENSE_RANK() also assigns the same rank to ties, but the next rank is the immediate next number, so there are no gaps.
Example code
SELECT emp_id,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees;