1

I have a query in PostgreSQL which returns:

id      operation   quantity   date
----------------------------------------
1282    WITHDRAW      20    2015-01-01
541     INCOMING      50    2015-01-01
4788    ACCEPT        17    2015-01-01
4789    ACCEPT        20    2015-01-01
.....

The query order sort the records by date...

However, I want to do a secondary order by operation: first INCOMING, then ACCEPT, then WITHDRAW. Order between records of the same operation is not important:

541     INCOMING      50    2015-01-01
4788    ACCEPT        17    2015-01-01
4789    ACCEPT        20    2015-01-01
1282    WITHDRAW      20    2015-01-01

or

541     INCOMING      50    2015-01-01
4789    ACCEPT        20    2015-01-01
4788    ACCEPT        17    2015-01-01
1282    WITHDRAW      20    2015-01-01

both ok.

I can not use the operation column nor the id column because it won't give the desired result...

Select ...
from ...
where ...
order by date

How can I manually specify the order I want..?

I'm looking for SQL syntax for something like:

order by date, (operation order by: INCOMING,ACCEPT,WITHDRAW)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
avi
  • 1,626
  • 3
  • 27
  • 45
  • Possible duplicate of [Postgresql: Ordering columns to match custom criteria](http://stackoverflow.com/questions/14713798/postgresql-ordering-columns-to-match-custom-criteria) or http://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order – Vivek S. Nov 17 '15 at 09:25

2 Answers2

4

Use an expression for the order by:

order by date, 
         case operation 
              when 'INCOMING' then 1
              when 'ACCEPT' then 2
              when 'WITHDRAW' then 3
              else 4 -- this is a fallback, anything else will be sorted at the end
         end
1

Nicer solution possible since Postgres v9.5.

ORDER BY date, 
     array_position(ARRAY['INCOMING', 'ACCEPT', 'WITHDRAW']::varchar[], operation)
rept
  • 2,086
  • 1
  • 26
  • 44