3

Is it possible to skip X first rows, and select all the other rows in one query? Like that:

abc
def
ghi
jkl
mno
========= start selecting all from here =========
pqr
stu
vwx
yz

And it will select: pqr, stu, vwx, yz

I've tried to accomplish that with LIMIT and OFFSET, but the problem is that the table is dynamic, and I don't know which LIMIT should I put (I don't know how many rows in the table).

ksssssq
  • 33
  • 1
  • 4

1 Answers1

6

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;
Community
  • 1
  • 1
JYelton
  • 32,870
  • 25
  • 119
  • 184
  • I think he wants to know how to dinamically set the `LIMIT` using some scope or anything in that order to do so with MySQL. – yoda Aug 10 '11 at 22:26
  • There's no way to set `LIMIT` dynamically, AFAIK - see the linked question for more info. – JYelton Aug 10 '11 at 22:31
  • Wow, that looks weird. I thought that it would be more elegant to somehow count how many rows in the table, and than subtract the offset (5), and this will be the limit. But thanks for the answer. – ksssssq Aug 10 '11 at 22:34
  • You definitely could issue a query like `SELECT COUNT(*) FROM table;` first to get a row count, then use that to calculate the `LIMIT`. I'd prefer doing it in one query instead of two. – JYelton Aug 10 '11 at 22:37
  • @JYelton - yes, I think that this solution will be faster anyway despite the fact that it looks ugly a bit. :-) – ksssssq Aug 10 '11 at 22:39
  • @ksssssq: Just so you don't lose precious bits of data by any evil chance, I would recommend you to use `18446744073709551615` instead of `18446744073709551610` (because the former is the actual maximum possible unsigned 8-byte integer, while the latter is just an upstart pretender). – Andriy M Aug 11 '11 at 05:44
  • @Andriy: Heh, you are correct. Wouldn't want to miss 5 rows out of 18 sextillion! (Fixed in my answer.) – JYelton Aug 11 '11 at 15:17