Which query correctly uses the OVER clause with ORDER BY to achieve this?
hard📝 Application Q15 of 15
SQL - Window Functions Fundamentals
You want to assign a rank to employees based on their salary, but if two employees have the same salary, they should get the same rank and the next rank should skip accordingly. Which query correctly uses the OVER clause with ORDER BY to achieve this?
ASELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
BSELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
CSELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
DSELECT employee_id, salary, RANK() OVER ORDER BY salary DESC AS salary_rank FROM employees;
Step-by-Step Solution
Solution:
Step 1: Understand ranking functions
RANK() assigns same rank to ties and skips ranks after ties; DENSE_RANK() assigns same rank but no skips; ROW_NUMBER() assigns unique numbers.
Step 2: Check syntax and behavior
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; uses RANK() with correct OVER(ORDER BY salary DESC) syntax, matching requirement. SELECT employee_id, salary, RANK() OVER ORDER BY salary DESC AS salary_rank FROM employees; has syntax error. ROW_NUMBER() and DENSE_RANK() do not skip ranks after ties.
Final Answer:
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -> Option A
Quick Check:
RANK() with OVER(ORDER BY) skips ranks after ties [OK]
Quick Trick:Use RANK() OVER(ORDER BY) for ranks with gaps on ties [OK]
Common Mistakes:
Using ROW_NUMBER() which never skips ranks
Using DENSE_RANK() which does not skip ranks
Missing parentheses in OVER clause
Master "Window Functions Fundamentals" in SQL
9 interactive learning modes - each teaches the same concept differently
