ROWS is a reserved keyword used in two primary contexts:1. Window frame clause - Inside an OVER() specification, ROWS tells the database to frame the result set by physical row offsets instead of value‐based ranges. It works with BETWEEN, UNBOUNDED PRECEDING, CURRENT ROW, FOLLOWING, etc. The frame determines which rows are visible to window functions such as SUM(), AVG(), or ROW_NUMBER().2. FETCH FIRST … ROWS ONLY / WITH TIES - In a SELECT statement that includes ORDER BY, ROWS specifies the maximum number of rows the query should return. This usage is ANSI-compliant pagination and an alternative to LIMIT.Key behaviors:- ROWS in window frames is evaluated after WHERE, GROUP BY, and HAVING but before the final ORDER BY.- Window frames default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if no frame clause is supplied; explicitly adding ROWS removes value based grouping and uses physical position instead.- The FETCH form requires ORDER BY for deterministic results and can optionally include WITH TIES to include rows that match the last ordered value.Caveats:- Not all databases treat ROWS and RANGE identically; some prohibit mixed frame types in the same query.- Omitting BOTH start and end boundaries after ROWS BETWEEN raises a syntax error.- FETCH FIRST … ROWS ONLY cannot be used in UPDATE…RETURNING in some dialects.
frame_start
(keyword) - integer|||UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROWframe_end
(keyword) - integer|||CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWINGn
(integer) - Positive number of rows to fetch in FETCH clauseSQL:2003 (window frames) and SQL:2008 (FETCH FIRST … ROWS ONLY)
Most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This groups ties together instead of counting physical rows.
Use LAG() or specify ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING to isolate exactly one prior row.
Yes. Place OFFSET ROWS before the FETCH clause to skip a number of rows then return the next set.
ROWS itself does not filter NULLs. If ORDER BY or partition columns contain NULLs, use ORDER BY ... NULLS FIRST/LAST or COALESCE to position them explicitly.