Custom ORDER BY Explanation

Question

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?

So, if I don't understand something, even though I like the elegance and simplicity of a solution, I should hold my hands over my ears and repeat "La, la, la". I don't like this approach; I would rather learn something new and become comfortable with it. — Evil Elf, Mar 02 '11 at 15:30

score 86 · Answer 1 · edited May 23 '17 at 11:55

86

@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)

edited May 23 '17 at 11:55

Community

1
1

answered Dec 29 '16 at 19:28

Daniil Ryzhkov

7,416
2
41
58

tried it on my dev instance, was great, production still on 9.3 .. not so great – DarkMukke May 22 '17 at 12:53
5

This applies to columns of type `Text`, I tried this with a column type `character` and the function threw an error - hopefully this helps :) – Sam J Sep 07 '17 at 03:41
3

A similar question that will also help with any casting you may need to do: https://dba.stackexchange.com/questions/201837/order-by-gives-error-function-array-positiontext-character-varying-does-no – Evan Pon Nov 19 '18 at 19:58
Hey Daniil, can I do same thing in slick for postgres? – Vaibhav Grover Mar 18 '20 at 19:20
Not really sure, probably worth asking separate question – Daniil Ryzhkov Mar 19 '20 at 20:24

score 63 · Answer 2 · answered Nov 03 '10 at 15:53

63

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

answered Nov 03 '10 at 15:53

Scott Bailey

7,748
2
23
21

9

should be `array_position` – Eben Geer Oct 20 '16 at 20:21
4

@EbenGeer only on 9.5 and above – DarkMukke May 22 '17 at 12:54
So with 9.5+ one can simply use `array_position` here? @DarkMukke I'd add this to your answer. – jayarjo Jan 14 '21 at 10:53

musiKk · Accepted Answer · 2010-11-03T15:44:09.443

50

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

edited Nov 03 '10 at 15:44

answered Nov 03 '10 at 15:38

musiKk

14,751
4
55
82

4

This is purely a thought. But I'd assume that it orders false before true, because of their respected integer values of 0 and 1 where 0 (false) comes before 1 (true). – Michael Lynch Aug 19 '13 at 17:53
It worked amazingly for me across every SQL server I threw it at (PostgreSQL, MySQL and SQLite)!! – Theodore R. Smith Sep 28 '18 at 17:56

score 12 · Answer 4 · answered Sep 12 '17 at 09:11

12

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);

answered Sep 12 '17 at 09:11

lifeiscontent

593
5
18

score 3 · Answer 5 · answered Nov 30 '22 at 04:27

This is to sort by a given list of values.

Since Postgres 9.4, a clean solution is to LEFT JOIN to an unnested array WITH ORDINALITY and ORDER BY the resulting ordinality number:

SELECT ...
FROM   ...
LEFT   JOIN unnest ('{Nails,Bolts,Washers,Screws,Staples,Nuts}'::text[]) WITH ORDINALITY sort(type, ord) USING (type)
ORDER  BY sort.ord ...

fiddle

See:

ORDER BY the IN value list

Custom ORDER BY Explanation

5 Answers5

Linked