无法使用标准SQL时，如何在Legacy SQL中实现结果分页？

Hey, I totally get the frustration here—Legacy SQL's lack of OFFSET makes pagination feel way harder than it should be, especially when you just need to skip rows without sorting. Here's a reliable way to replicate that "skip N, take M" behavior you're after:

Step-by-Step Implementation #

Use a subquery with ROW_NUMBER() to assign a unique sequential number to each row, then filter for the range you need. For your example (skip 2000 rows, fetch 1000), the query would look like this:

SELECT *
FROM (
  -- Inner query adds a row number to every record
  SELECT *, ROW_NUMBER() OVER() AS row_num
  FROM your_target_table
) numbered_rows
-- Filter for rows after the first 2000, up to the next 1000
WHERE row_num > 2000 AND row_num <= 3000;

How This Works #

• The inner query uses ROW_NUMBER() OVER() to generate a unique integer for each row, following the default order the database returns results (since you don't need explicit sorting).
• The outer query then selects only rows where the generated row number falls in your target range: >2000 skips the first 2000 rows, and <=3000 grabs the next 1000.

Quick Note #

Since you're not sorting, keep in mind that if the underlying data changes (like new rows being added or existing ones removed), subsequent runs of this query might return slightly different results. But if your use case doesn't require stable pagination across data changes, this is perfect for your needs.

内容的提问来源于stack exchange，提问作者jeremieca