MySQL Offset Infinite Rows

MySQL offset infinite rows

From the MySQL Manual on LIMIT:

To retrieve all rows from a certain
offset up to the end of the result
set, you can use some large number for
the second parameter. This statement
retrieves all rows from the 96th row
to the last:

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

SQL offset only

If you just want the last N rows, try this:

SELECT field1, field2 FROM table
ORDER BY some_column DESC
LIMIT N;

This gives you the last few records based on the order of some_column.

You can use an auto-incrementing primary key (hopefully there is one) to establish the order of the rows if nothing else can be used.

If instead you want to skip the first X rows (which you indicate in the question) and get all rows until the end, try this:

SELECT field1, field2 FROM table
ORDER BY some_column ASC
LIMIT 18446744073709551615 OFFSET X;

For this latter case, see: MySQL Offset Infinite Rows

Though, if you do have an auto-incrementing primary key, and it corresponds with the rows you want to select, I recommend:

SELECT field1, field2 FROM table
WHERE id > X;

Selecting all records using SQL LIMIT and OFFSET query

From the MySQL documentation:

To retrieve all rows from a certain offset up to the end of the result
set, you can use some large number for the second parameter. This
statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

So getting all rows might look as follows:

SELECT * FROM tbl LIMIT 0,18446744073709551615;

selecting all rows except top row using offset in MySQL

Sadly in MySQL OFFSET only works together with the LIMIT clause. So you need to use

SELECT * FROM EMPLOYEES ORDER BY SALARY LIMIT 18446744073709551615 OFFSET 1;

or

SELECT * FROM EMPLOYEES ORDER BY SALARY LIMIT 1, 18446744073709551615;

I have chosen that limit number from a different question

OFFSET without LIMIT in mysql

No there is no way. You need to provide the LIMIT.

Then manual says:

To retrieve all rows from a certain offset up to the end of the result
set, you can use some large number for the second parameter.

Which rows are returned when using LIMIT with OFFSET in MySQL?

It will return 18 results starting on record #9 and finishing on record #26.

Start by reading the query from offset. First you offset by 8, which means you skip the first 8 results of the query. Then you limit by 18. Which means you consider records 9, 10, 11, 12, 13, 14, 15, 16....24, 25, 26 which are a total of 18 records.

Check this out.

And also the official documentation.

MySQL LIMIT/OFFSET: get all records except the first X

No, sorry. From the MySQL Documentation:

To retrieve all rows from a certain
offset up to the end of the result
set, you can use some large number for
the second parameter. This statement
retrieves all rows from the 96th row
to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Offset MySQL Without Limit

Basically, no. Limit must be supplied. See this SO question for a solution.

---

Related Topics