5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mital Pritmani
  • 4,880
  • 8
  • 38
  • 39
  • 1
    "Without use of order by" is logical nonsense. In a query without `ORDER BY`, the order is undefined and there are no "last" rows. You probably mean "without the use of `LIMIT`". – Erwin Brandstetter Oct 31 '11 at 18:08
  • Hi Erwin, if I don't want to use order by, it means I want the last added n rows. And the solution given by Lukas gives me that result.Database also maintains the order in which the rows are inserted (which is said a natural order) and I wanted such last n rows. – Mital Pritmani Nov 01 '11 at 09:49
  • 1
    This is unreliable. There is no "natural order" in an RDBMS by definition. I have more to write than what a comment will hold, please see my answer. – Erwin Brandstetter Nov 01 '11 at 12:47
  • @mitalpritmani: Erwin is right. You *shouldn't* assume that you will reliably get the last n rows in the physical table. You might at least want to consider ordering by `oid` or even better, by your own primary key. Then, of course, the whole question is obsolete, as you can `ORDER BY oid DESC LIMIT :n`... – Lukas Eder Nov 01 '11 at 12:50
  • 1
    As a side note, the OID column is not added to user tables anymore (since 9.0 if I'm correct.) http://www.postgresql.org/docs/9.0/static/datatype-oid.html – Alexis Wilke Jun 13 '14 at 21:04
  • OP asks about tables, but If you consider functions such as `ts_debug` that return records in a certain order, but don't provide a column to do `order by desc` on - then this is a valid and useful question. – Vsevolod Golovanov May 15 '21 at 17:09

2 Answers2

10

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hey Erwin, thanks for your information. I think I had missed these points. I read the manual and came to know about this point which you have quoted here. So does this mean that I can't have last inserted rows from database and I will always have to use order by? Please give me answers to these questions. – Mital Pritmani Nov 02 '11 at 05:18
  • @mitalpritmani: Yes, that't what it means. If you want the "last n rows" reliably, you **have to** `ORDER BY` (thereby specifying, what "last" means exactly). Everything else is pure luck. If you don't have columns to identify the "last" rows, you'll have to add something like a [sequence](http://www.postgresql.org/docs/current/interactive/sql-createsequence.html). – Erwin Brandstetter Nov 02 '11 at 12:30
6

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
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Hey Lukas, your previous answer was easy to understand, I mean with both queries. Can you please put that also as an example so that I can accept your answer? And ya, this query should be starting with "select t2.* from(select t1.*, .....)" so please correct it. If I write t1 first and then t2, it gives me error - missing FROM-clause entry for table "t2". – Mital Pritmani Oct 31 '11 at 07:44
  • @mitalpritmani: You're right, I got a syntax error there... Corrected, and restored my previous example – Lukas Eder Oct 31 '11 at 07:58
  • @LukasEder: This answer is misleading in that it supports the superstition of the OP he could rely on a natural order without `ORDER BY`. See my answer about that. – Erwin Brandstetter Nov 01 '11 at 12:45
  • @ErwinBrandstetter: Good point. I was assuming, though, that the OP is aware of that. That's why I said that my solution is generic in a way that it can deliver the last n rows of any query, no matter how sorting is applied – Lukas Eder Nov 01 '11 at 12:47
  • 1
    And since the order is unknown, the _last N records_ is semantically equivalent to _the first N records_, or _any N records_. – wildplasser Nov 01 '11 at 13:26