CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
notthisname
notthisname

Reputation: 13

ORDER BY column = value desc|asc

I found this piece of code a while ago, and I don't seem to find any explanation about how it really works:

SELECT account_id from accounts order by account_id = 100;

So, I know what order by [column] desc|asc does to the result set. But I don't seem to find the explanation for giving a value to the [column] and how that affects the result set. It's clearly affected, but I don't seem to find a pattern.

Upvotes: 1

Views: 224

Answers (3)

lalit
lalit

Reputation: 1

Basically ORDER BY is of two types ASC and DESC By Default it is ASC

Let us take an example

SELECT * from Person
ORDER BY Age DESC

Above query returns the Age of Persons in Descending Order

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Postgres supports boolean types, which take on two values "true" and "false" (plus NULL, of course). A boolean value is being used as the order by key.

The expression account_id = 100 evaluates to "true" for account_id 100 and false for others (or NULL).

What does this do? Well, "true" > "false" and the ordering is ascending. Hence, the true value is ordered after all other values; account_id 100 goes at the end. Well not quite the end. NULL values are lastest -- they would go at the very end.

More commonly, this is done with a descending sort:

order by (account_id = 100) desc

This puts account 100 first in the list.

Note: I put the expression in parentheses in such cases to make it clear that the intent really is to order by the expression. That is, there is no typo.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Try rewriting your query using an explicit CASE expression in the ORDER BY clause:

SELECT account_id
FROM accounts
ORDER BY CASE WHEN account_id = 100 THEN 1 ELSE 0 END;

You will observe that all records having account_id != 100 will appear before all records where this is true. When you use:

ORDER BY account_id = 100

Then you are ordering by the boolean equality itself. So, when not true, it would evaluate to zero, and when true would evaluate to one.

Upvotes: 1

Related Questions