I want to fetch the last n rows from a table in a Postgres database. I don't want to use an ORDER BY
clause as I want to have a generic query. Anyone has any suggestions?
A single query will be appreciated as I don't want to use FETCH
cursor of Postgres.
SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.
The TOP clause in SQL Server returns the first N number of records or rows from a table. Applying the ORDER BY clause with DESC, will return rows in descending order. Hence, we get the last 3 rows.
METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.
Although the SQL standard doesn't allow a SELECT statement without a FROM clause, pretty much every other database does support the construct of selecting and expression without a FROM clause.
That you get what you expect with Lukas' solution (as of Nov. 1st, 2011) is pure luck. There is no "natural order" in an RDBMS by definition. You depend on implementation details that could change with a new release without notice. Or a dump / restore could change that order. It can even change out of the blue when db statistics change and the query planner chooses a different plan that leads to a different order of rows.
The proper way to get the "last n" rows is to have a timestamp or sequence column and ORDER BY
that column. Every RDBMS you can think of has ORDER BY
, so this is as 'generic' as it gets.
The manual:
If
ORDER BY
is not given, the rows are returned in whatever order the system finds fastest to produce.
Lukas' solution is fine to avoid LIMIT
, which is implemented differently in various RDBMS (for instance, SQL Server uses TOP n
instead of LIMIT
), but you need ORDER BY
in any case.
Use window functions!
select t2.* from (
select t1.*, row_number() over() as r, count(*) over() as c
from (
-- your generic select here
) t1
) t2
where t2.r + :n > t2.c
In the above example, t2.r
is the row number of every row, t2.c
is the total records in your generic select. And :n
will be the n last rows that you want to fetch. This also works when you sort your generic select.
EDIT: A bit less generic from my previous example:
select * from (
select my_table.*, row_number() over() as r, count(*) over() as c
from my_table
-- optionally, you could sort your select here
-- order by my_table.a, my_table.b
) t
where t.r + :n > t.c
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With