Custom ORDER BY Explanation

postgresql sql-order-by
Evil Elf picture Evil Elf · Nov 3, 2010 · Viewed 20.5k times · Source

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?

Answer

Scott Bailey picture Scott Bailey · Nov 3, 2010

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

Related questions

PostgreSQL DISTINCT ON with different ORDER BY

I want to run this query: SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM purchases WHERE purchases.product_id = 1 ORDER BY purchases.purchased_at DESC But I get this error: PG::Error: ERROR: SELECT DISTINCT ON expressions must …

Read More
sql ORDER BY multiple values in specific order?

Ok I have a table with a indexed key and a non indexed field. I need to find all records with a certain value and return the row. I would like to know if I can order by multiple values. …

Read More
ORDER BY the IN value list

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause: SELECT * FROM comments WHERE (comments.id IN (1,3,2,4)); This returns comments in …

Read More