MySQL limit from descending order

Question

Is it available to write a query to use same "LIMIT (from), (count)", but get result in backwards?

In example if I have 8 rows in the table and I want to get 5 rows in two steps I would: first step query:

select * from table limit 0, 5 

first step result:

first 5 rows; 

second step query:

select * from table limit 5, 5 

second step result:

last 3 rows; 

But I want to get it vice versa. I mean from the first step I want last 3 rows and from the second I want 5 first rows. Thank you for your answer

Answer 1

No, you shouldn't do this. Without an ORDER BY clause you shouldn't rely on the order of the results being the same from query to query. It might work nicely during testing but the order is indeterminate and could break later. Use an order by.

SELECT * FROM table1 ORDER BY id LIMIT 5 

By the way, another way of getting the last 3 rows is to reverse the order and select the first three rows:

SELECT * FROM table1 ORDER BY id DESC LIMIT 3 

This will always work even if the number of rows in the result set isn't always 8.

Answer 2

Let's say we have a table with a column time and you want the last 5 entries, but you want them returned to you in asc order, not desc, this is how you do it:

select * from ( select * from `table` order by `time` desc limit 5 ) t order by `time` asc