When the last rows of an ordered set are required, it is usual to create a derived table and reorder. For example, to return the last 3 elements of a table with an auto incremented
SELECT * FROM ( SELECT * FROM table ORDER BY id DESC LIMIT 3 ) t ORDER BY t.id
LIMIT can also have an offset, the same result could be achieved if we know the number of lines (say 10) in advance with this query:
SELECT * FROM table ORDER BY id LIMIT 3 OFFSET 7
Is it possible to run a subquery to
count(*) a table and dynamically build a
LIMIT using that number?
SELECT * FROM table ORDER BY id LIMIT 3 OFFSET [ select count() -3 ]Answer1:
No, it isn't possible to specify a dynamic offset.
Your original query with a subquery is the easiest way to do this.