mysql - offset problem

Question

I recently posted a question about getting last 3 results in table in the correct order. I now want the get all comments apart from the last 3 in the correct order.

Here is my syntax;

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      OFFSET 3) AS T 
ORDER BY TIME_STAMP

The error I am receiving is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET, 3) AS T ORDER BY TIME_STAMP' at line 1

I can't seem to get it to work. Any help much appreciated.

Senseful · Accepted Answer · 2010-06-17T07:47:13.787

8

According to 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:

They recommend you use a query such as:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

So in your case, you should try:

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      LIMIT 3,18446744073709551615) AS T 
ORDER BY TIME_STAMP

Note that you can also use the PostgreSQL compatible version using the keyword OFFSET:

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      LIMIT 18446744073709551615 OFFSET 3) AS T 
ORDER BY TIME_STAMP

Just in case you are wondering, 18446744073709551615 = 2^64 - 1.

edited Jun 17 '10 at 07:47

answered Jun 17 '10 at 07:28

Senseful

73,679
56
267
405

I think the OP is looking for the opposite. Getting all the records except for the last 3. – Daniel Vassallo Jun 17 '10 at 07:30
@Daniel: That's what this statement does. It skips the first 3 and returns the rest. – Senseful Jun 17 '10 at 07:30
Actually, that query can do whichever you want simply by replacing DESC with ASC :) – Nicholas Knight Jun 17 '10 at 07:33
@Nicholas: You would need to know how many rows to select if you reverse the ordering so that you don't get the last 3 (i.e. count - 3). – Senseful Jun 17 '10 at 07:36
I had to change the offset to 3 but perfect. – Phil Jackson Jun 17 '10 at 07:40
@eagle: Interesting... I wasn't aware of this :) +1 – Daniel Vassallo Jun 17 '10 at 07:41
@eagle: Right, I meant with regard to Daniel's comment (as I interpreted it), you can use your query to get all but the _last_ 3 _or_ all but the _first_ 3 just by changing DESC/ASC. – Nicholas Knight Jun 17 '10 at 07:59
@Nicholas: my bad, I assumed you were talking to me since I didn't see an @ in your comment. – Senseful Jun 17 '10 at 08:22

zed_0xff · Answer 2 · 2010-06-17T07:31:29.440

5

You can't use OFFSET without a LIMIT.

A little bulky, but that query worked for me, and not worked without an redundant internal subquery (mysql 5.0.90)

select * from $table 
where id not in (
  select id from (
    select id from languages order by id DESC LIMIT 3
  ) l1
) order by time_stamp

edited Jun 17 '10 at 07:31

answered Jun 17 '10 at 07:23

zed_0xff

29,646
7
48
70

score 0 · Answer 3 · answered Aug 22 '19 at 02:18

0

Was having the same issue because I copied:

OFFSET :offset LIMIT :limit

from an existing query, which is valid in Postgres.

Apparently the order matters in MySQL, so reversing the order worked for me:

LIMIT :limit OFFSET :offset

answered Aug 22 '19 at 02:18

Geoherna

3,035
1
22
36

mysql - offset problem

3 Answers3

Linked