Adding order by with offset and limit in mysql query

Question

I have a mysql query

SELECT * FROM lead LIMIT 5 OFFSET 0

to select data from the table lead and limit the results to 5 with offset of 0. I would like to order the results by its id by desc, so the results will be populated as the last added data first.

I tried

SELECT * FROM lead LIMIT 5 OFFSET 0 order by id desc

but it's not working. Please correct me where am wrong.

Dennis · Accepted Answer · 2011-11-16 09:18:54Z

62

You have to

select * from lead order by id desc LIMIT 5 OFFSET 0

The manual ( http://dev.mysql.com/doc/refman/5.0/en/select.html ) describes that LIMIT is only allowed to appear after the ORDER BY.

answered Nov 16, 2011 at 9:18

Dennis

2,1425 gold badges21 silver badges29 bronze badges

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

1 Comment

codeforester Over a year ago

As per the doc: For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. LIMIT x OFFSET y is definitely more explicit and less confusing than LIMIT x, y.

codeforester · Accepted Answer · 2020-12-08 04:44:10Z

7

The ORDER BY clause should come before the LIMIT clause. This makes sense because you first want the record set to be ordered and then apply the limitation.

SELECT * FROM lead ORDER BY id DESC LIMIT 0, 5

You can use either LIMIT offset, row_ count syntax or the LIMIT row_count OFFSET offset.

Check: http://dev.mysql.com/doc/refman/5.0/en/select.html

edited Dec 8, 2020 at 4:44

codeforester

43.8k21 gold badges123 silver badges159 bronze badges

answered Nov 16, 2011 at 9:17

CodeZombie

5,3753 gold badges32 silver badges37 bronze badges

Collectives™ on Stack Overflow

Adding order by with offset and limit in mysql query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related