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-17 07:47:13Z

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, 2010 at 7:47

answered Jun 17, 2010 at 7:28

Senseful

92.7k73 gold badges339 silver badges497 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Daniel Vassallo Over a year ago

I think the OP is looking for the opposite. Getting all the records except for the last 3.

Senseful Over a year ago

@Daniel: That's what this statement does. It skips the first 3 and returns the rest.

Nicholas Knight Over a year ago

Actually, that query can do whichever you want simply by replacing DESC with ASC :)

Senseful Over a year ago

@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).

Phil Jackson Over a year ago

I had to change the offset to 3 but perfect.

|

zed_0xff · Accepted Answer · 2010-06-17 07:31:29Z

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, 2010 at 7:31

answered Jun 17, 2010 at 7:23

zed_0xff

33.6k8 gold badges55 silver badges72 bronze badges

Comments

Geoherna · Accepted Answer · 2019-08-22 02:18:36Z

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, 2019 at 2:18

Geoherna

3,6241 gold badge26 silver badges41 bronze badges

Collectives™ on Stack Overflow

mysql - offset problem

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related