Select physically last record without ORDER BY

Question

An application inherited by me was oriented on so to say "natural record flow" in a PostgreSQL table and there was a Delphi code:

query.Open('SELECT * FROM TheTable');
query.Last();

The task is to get all the fields of last table record. I decided to rewrite this query in a more effective way, something like this:

SELECT * FROM TheTable ORDER BY ReportDate DESC LIMIT 1

but it broke all the workflow. Some of ReportDate records turned out to be NULL. The application was really oriented on a "natural" records order in a table.

How to do a physically last record selection effectively without ORDER BY?

Have you seen this QA? https://stackoverflow.com/questions/20050341/when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record - when there is no `ORDER BY` then the order of returned results is **undefined** - there is no "natural order" - it could change based on the current phase of the moon or if you ever do a table garbage-collection if your DBMS reorders rows to be efficient. — Dai, Aug 18 '17 at 05:08
@Dai: Thank you, I didnt see that QA. The key phrase there is **"might change over time"**. So, I will try to stay with ORDER BY. — Paul, Aug 18 '17 at 06:34

Vao Tsun · Accepted Answer · 2018-06-19T15:10:18.040

to do a physically last record selection, you should use ctid - the tuple id, to get the last one - just select max(ctid). smth like:

t=# select ctid,* from t order by ctid desc limit 1;
  ctid  |               t               
--------+-------------------------------
 (5,50) | 2017-06-13 11:41:04.894666+00
(1 row)

and to do it without order by:

t=# select t from t where ctid = (select max(ctid) from t);
               t               
-------------------------------
 2017-06-13 11:41:04.894666+00
(1 row)

Its worth knowing that you can find ctid only after sequential scan. so checking the latest physically row will be costy on large data sets

Select physically last record without ORDER BY

1 Answers1