2

I want to run a SQL query in Postgres that is exactly the reverse of the one that you'd get by just running the initial query without an order by clause.

So if your query was:

SELECT * FROM users

Then

SELECT * FROM users ORDER BY <something here to make it exactly the reverse of before>

Would it just be this?

ORDER BY Desc
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
chris
  • 1,869
  • 4
  • 29
  • 52

2 Answers2

14

You are building on the incorrect assumption that you would get rows in a deterministic order with:

SELECT * FROM users;

What you get is really arbitrary. Postgres returns rows in any way it sees fit. For simple queries typically in order of their physical storage, which typically is the order in which rows were entered. But there are no guarantees, and the order may change any time between two calls. For instance after any UPDATE (writing a new physical row version), or when any background process reorders rows - like VACUUM. Or a more complex query might return rows according to an index or a join. Long story short: there is no reliable order for table rows in a relational database unless you specify it with ORDER BY.

That said, assuming you get rows from the above simple query in the order of physical storage, this would get you the reverse order:

SELECT * FROM users
ORDER  BY ctid DESC;

ctid is the internal tuple ID signifying physical order. Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

here is a tsql solution, thid might give you an idea how to do it in postgres

select * from (
SELECT *, row_number() over( order by (select 1)) rowid 
  FROM users 
  ) x 
  order by rowid desc
Kostya
  • 1,567
  • 1
  • 9
  • 15
  • 2
    What does TSQL have to do with PostgreSQL? – mu is too short Jun 23 '16 at 01:13
  • 2
    The answer isn't actually T-SQL specific as window functions are part of the SQL standard and have been supported by Postgres for a long time. But you can use `row_number() over ()` in Postgres. –  Jun 23 '16 at 06:08