Window Functions · SQL SQL
Explain the difference between the ROWS and RANGE framing clauses in SQL window functions.
Practice question from Window Functions (SQL SQL), with the correct answer and a step-by-step solution.
hard · Subjective · Q-80B0C3
Explain the difference between the ROWS and RANGE framing clauses in SQL window functions.
Answer: The ROWS frame clause defines the window based on physical offsets of rows relative to the current row (e.g., exactly 2 rows preceding). The RANGE frame clause defines the window based on logical offsets of values relative to the current row's value in the ORDER BY expression. Furthermore, RANGE handles peer rows with identical ordering values together, whereas ROWS treats physical rows independently.
Solution
The response should clearly distinguish physical row counting (ROWS) from logical value-based offsets (RANGE), including how peer groups are handled.
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?
- Consider the following query: SELECT employeeid, departmentid, salary, ROWNUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) as…
- 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…
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