CODEWITHSUNDEEP
mysql
Bala
Bala

Reputation: 3638

Adding order by with offset and limit in mysql query

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.

Upvotes: 32

Views: 67556

Answers (2)

CodeZombie
CodeZombie

Reputation: 5377

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

Upvotes: 7

Dennis
Dennis

Reputation: 2142

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.

Upvotes: 62

Related Questions