Member Avatar for Venom Rush

As the title says, I'd like to select a certain number of rows from a table starting from a specified row.

So for example, I have a table with 250 rows and the rows are numbered incrementally using a column called 'ref'. I now want to select rows 51-100. How would I go about doing that?

  • 1 Contributor
  • 1 Reply
  • 106 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Venom Rush

Dani AI

Generated

A short clarification tied to ’s post: there are two different things people mean by “rows 51–100” — a range of ref values, or the 51st through 100th rows in a result set. Using a ref range is fine if ref is a dense, monotonic id and you really want those id values. If ref has gaps (deleted rows) or you want the ordinal slice of the result, use LIMIT/OFFSET and a deterministic ORDER BY.

Use LIMIT with ORDER BY to get rows 51–100 (offset is zero‑based):

SELECT *
FROM your_table
ORDER BY ref
LIMIT 50 OFFSET 50;

For large tables, OFFSET can get slow because the engine skips rows. For efficient paging where you always move forward, use keyset pagination (replace :last_ref with the last seen ref):

SELECT *
FROM your_table
WHERE ref > :last_ref
ORDER BY ref
LIMIT 50;

If the goal is “the 51st through 100th rows by arbitrary ordering” and you need exactly that ordinal slice even when ref isn’t contiguous, use ROW_NUMBER() (MySQL 8+):

SELECT *
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY ref) AS rn
  FROM your_table AS t
) AS numbered
WHERE rn BETWEEN 51 AND 100;

Always include an ORDER BY for deterministic results and keep ref indexed for fast range scans. See the MySQL docs for the LIMIT clause and for window functions: MySQL LIMIT documentation and MySQL window functions.

Member Avatar for Venom Rush

I solved my problem by using the following:

WHERE ref BETWEEN 1 AND 50
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.