SQL Keywords

SQL ROWS

What does the SQL ROWS keyword do?

Defines the frame of rows that a window function can access or limits the number of rows returned by FETCH.
Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ROWS:

SQL ROWS Full Explanation

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.

SQL ROWS Syntax

-- Window frame
<window_function>() OVER (
  PARTITION BY <expr>
  ORDER BY <expr>
  ROWS BETWEEN <frame_start> AND <frame_end>
);

-- Fetch-first
SELECT <columns>
FROM <table>
ORDER BY <expr>
FETCH FIRST <n> ROWS ONLY;

SQL ROWS Parameters

  • frame_start (keyword) - integer|||UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW
  • frame_end (keyword) - integer|||CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING
  • n (integer) - Positive number of rows to fetch in FETCH clause

Example Queries Using SQL ROWS

-- 1. Rolling 7-day sum of orders
SELECT order_date,
       SUM(total) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_day_sales
FROM orders;

-- 2. Top 5 highest-priced products
SELECT id, name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

Expected Output Using SQL ROWS

  • Each row shows total sales for the current day plus the previous six days.
  • Query returns exactly 5 rows (or more if WITH TIES is used and there are price ties).

Use Cases with SQL ROWS

  • Create moving averages, running totals, or lag calculations based on a fixed row window.
  • Paginate or limit result sets in a standards-compliant way.
  • Compare ROWS vs RANGE performance or correctness when dealing with non-numeric ORDER BY columns.

Common Mistakes with SQL ROWS

  • Using RANGE when physical row offsets are needed, leading to unexpected aggregates.
  • Forgetting ORDER BY in FETCH FIRST … ROWS ONLY and receiving arbitrary rows.
  • Supplying only one boundary in ROWS BETWEEN without CURRENT ROW/UNBOUNDED keyword.

Related Topics

First Introduced In

SQL:2003 (window frames) and SQL:2008 (FETCH FIRST … ROWS ONLY)

Frequently Asked Questions

What is the default window frame when I omit ROWS?

Most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This groups ties together instead of counting physical rows.

How do I get the previous row's value using ROWS?

Use LAG() or specify ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING to isolate exactly one prior row.

Can I combine OFFSET with FETCH FIRST … ROWS ONLY?

Yes. Place OFFSET ROWS before the FETCH clause to skip a number of rows then return the next set.

Why does my window query ignore NULLS?

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.

Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.
Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo

Check out other commonly used SQL Keywords!