# Custom ORDER BY Explanation

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?

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
```

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

@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)
``````
``````select array_position(array['foo'::char,'bar','baz'::char], 'bar');