MySQL limit a query with beginning at end

Question

Assuming I have a MySQL Table which contains two columns and I perform (it could not be simpler) a query

select * from test order by prio asc, datum asc

which gives me as expected this result:

datum      | prio
2017-07-02 | 1 
2017-07-09 | 2
2017-06-11 | 3
2017-06-01 | 4
2017-06-18 | 5
2017-06-25 | 6

Now, I want to limit my result to have ONLY the last 3 rows, with avoid doing something with an offset, because then I have to count the rows, so I know that this works:

select * from test order by prio asc, datum asc limit 3,3

But is there a trick, that I can do something similar to:

select * from test order by prio asc, datum asc limit -3

I know this fails.

Thank you

change your sort order to display the desired rows first and take the first three — Jonny, Jul 13 '17 at 12:46
`order by prio desc` instead of `asc`, then just get the first 3 rows. — mabako, Jul 13 '17 at 12:46
Possible duplicate of [Select last N rows from MySQL](https://stackoverflow.com/questions/12125904/select-last-n-rows-from-mysql) — underscore_d, Jul 13 '17 at 12:48
or https://stackoverflow.com/questions/573646/mysql-select-from-n-last-rows or https://stackoverflow.com/questions/2708992/mysql-limit-from-descending-order or etc. Do try to search! — underscore_d, Jul 13 '17 at 12:48

score 5 · Accepted Answer · answered Jul 13 '17 at 13:49

Try below query.

SELECT * FROM(
    SELECT * FROM test ORDER BY prio DESC LIMIT 0,3
) t ORDER BY prio ASC, datum asc;

Subquery will sort descending based on priority and return last 3 records. Outer query will again sort those priorities in ascending order and then sort on datum.

Hope this will help you.

MySQL limit a query with beginning at end

1 Answers1