Window Functions · SQL SQL
Consider the following query: SELECT employeeid, departmentid, salary, ROWNUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) as rn FROM e…
Practice question from Window Functions (SQL SQL), with the correct answer and a step-by-step solution.
easy · MCQ · Q-2841D6
Consider the following query: SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn FROM employees;. What does the PARTITION BY clause do?
- A.It sorts the entire result set by department_id before applying the window function.
- B.It divides the result set into subsets or partitions, and the window function is applied independently to each partition.Correct answer
- C.It filters out rows that do not match the specified department_id.
- D.It limits the number of rows returned per department to a single row.
Solution
The PARTITION BY clause divides the rows of the result set into logical groups (partitions). The window function computation is then reset and evaluated independently within each partition.
More from Window Functions
- What is the primary difference between using a standard aggregate function with GROUP BY and using a window function in SQL?
- Which ranking window function assigns the same rank to identical values with no gaps in ranking sequence, meaning if two rows share rank 1,…
- What is the default frame specification in a window function when an ORDER BY clause is provided, but no explicit ROWS or RANGE frame is sp…
- Explain the difference between the ROWS and RANGE framing clauses in SQL window functions.
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed