CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
chris
chris

Reputation: 2020

Reverse initial order of SELECT statement

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

Upvotes: 2

Views: 11157

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

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:

Upvotes: 14

Kostya
Kostya

Reputation: 1605

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

Upvotes: 4

Related Questions