0

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
  • 704,970
  • 168
  • 1,303
  • 1,425
avi
  • 1,436
  • 3
  • 22
  • 40
  • 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
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
0

Nicer solution possible since Postgres v9.5.

ORDER BY date, 
     array_position(ARRAY['INCOMING', 'ACCEPT', 'WITHDRAW']::varchar[], operation)
rept
  • 1,916
  • 1
  • 24
  • 42