Is there a way in MySQL to have the first 10 result from a SELECT query skipped? I'd like it to work something like LIMIT.
Asked
Active
Viewed 1.4e+01k times
8 Answers
164
Use LIMIT with two parameters. For example, to return results 11-60 (where result 1 is the first row), use:
SELECT * FROM foo LIMIT 10, 50
For a solution to return all results, see Thomas' answer.
Community
- 1
- 1
Dominic Rodger
- 97,747
- 36
- 197
- 212
119
There is an OFFSET as well that should do the trick:
SELECT column FROM table
LIMIT 10 OFFSET 10
jamesaharvey
- 14,023
- 15
- 52
- 63
61
-
I think ```SELECT * FROM foo LIMIT 10, 50``` approach is better if our intension is just pagination. – Ayemun Hossain Ashik Jun 13 '23 at 11:40
37
From the manual:
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;
Obviously, you should replace 95 by 10. The large number they use is 2^64 - 1, by the way.
Thomas
- 174,939
- 50
- 355
- 478
-
Wow, this makes the query take a huge ammount of time. Weird recommendation for their manual. – S. Dre May 20 '22 at 06:52
7
LIMIT allow you to skip any number of rows. It has two parameters, and first of them - how many rows to skip
Your Common Sense
- 156,878
- 40
- 214
- 345
-
This made me clear that "What is Offset" Thanks @Col. Shrapnel – OM The Eternity Feb 23 '11 at 02:32
-
and if there is no second parameter, then the first parameter is not the number to skip, but the number to take. This is a poor design in my opinion. I stick with OFFSET. – Daniel Williams Apr 10 '23 at 15:55
4
To skip first 10 rows use OFFSET 10, but LIMIT is also required,
If you want to get all the rows and only skip first 10 rows try using a big number like 999999999 or as per your choice
SELECT * FROM table LIMIT 999999 OFFSET 10
Vicky Salunkhe
- 9,869
- 6
- 42
- 59
2
select * from table where id not in (select id from table limit 10)
where id be the key in your table.
mickeymoon
- 4,820
- 5
- 31
- 56
-
This is useful and fast to do, for impala and other databases where the limit with offset requires an order argument which is not always desirable. – KarthikS Apr 23 '17 at 22:26
-
1
-7
If your table has ordering by id, you could easily done by:
select * from table where id > 10
Biswajit Paul
- 5
- 2
-
2You are assuming that the first 10 records are all still there. Even if they are, if one is deleted at any point in the future that would break your method. – blogo Mar 10 '22 at 02:01
-