Custom ORDER BY Explanation
PostgresqlSql Order-ByPostgresql Problem Overview
I found this some time ago and have been using it since; however, looking at it today, I realized that I do not fully understand why it works. Can someone shed some light on it for me?
ORDER BY s.type!= 'Nails',
s.type!= 'Bolts',
s.type!= 'Washers',
s.type!= 'Screws',
s.type!= 'Staples',
s.type!= 'Nuts', ...
If I order by s.type, it orders alphabetically. If I use the example above it uses the same order as the line positions. What I don't understand is the use of !=. If I use = it appears in the opposite order. I cannot wrap my head around the concept of this.
It would reason to me that using = in place of the !='s above would place Nails first in position, but it does not, it place it in the last. I guess my question is this: Why do i have to use !=, not = in this situation?
Postgresql Solutions
Solution 1 - Postgresql
@Scott Bailey suggested great idea. But it can be even simpler (you don't have to create custom function) since PostgreSQL 9.5. Just use array_position
function:
ORDER BY array_position(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)
Solution 2 - Postgresql
Each expression gets evaluated as a bool and treated as 0 for false and 1 for true and sorted appropriately. Even though this works, the logic is hard to follow (and thus maintain). What I use is a function that finds a value's index in an array.
ORDER BY idx(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)
This is much easier to follow. Nails will be sorted first and nuts sorted last. You can see how to create the idx function in the Postgres snippets repository. http://wiki.postgresql.org/wiki/Array_Index
Solution 3 - Postgresql
I've never seen it but it seems to make sense.
At first it orders by s.type != 'Nails'
. This is false
for every row that contains Nails
in the type
column. After that it is sorted by Bolts
. Again for all columns that do contain Bolts
as a type
this evaluates to false. And so on.
A small test reveals that false
is ordered before true
. So you have the following: First you get all rows with Nails
on top because the according ORDER BY
evaluated to false
and false
comes first. The remaining rows are sorted by the second ORDER BY
criterion. And so on.
type | != Nails | != Bolts | != Washers 'Nails' | false | true | true 'Bolts' | true | false | true 'Washers' | true | true | false
Solution 4 - Postgresql
with array_position, it needs to have the same type that you're querying against.
e.g:
select array_position(array['foo'::char,'bar','baz'::char], 'bar');
select array_position(array['foo'::char,'bar','baz'::char], 'baz'::char);