Adding order by with offset and limit in mysql query

28
votes

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.

2
mysql

2 Answers

55
votes

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.

5
votes

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