PostgreSQL - conditional ordering

Question

I have the following table:

key | date         | flag
--------------------------
1    now()           true
2    now() - 1 hour  true
3    now() + 1 hour  true
4    now()           false
5    now() - 1 hour  false
6    now() + 1 hour  false

I want the following sorting:

First, all rows with flag = false. These rows must be sorted with date asc.
Then, all other rows (flag = true). However, these rows must be sorted with date desc.

Is the following query correct?

(
    select *
    from test
    where flag = false
    order by date asc
)
union all
(
    select *
    from test
    where flag = true
    order by date desc
)

Is there a better way to do this? Will the union all keep the rows sorted, and therefore just concatenate the output of the two inner queries?

I don't know how to repeat the columns in the order by, based on a condition.

update

Fiddle can be found here: http://rextester.com/FFOSS79584

smth like order by flag,case when flag then date end desc, case when not flag then date end asc rextester.com/FDAY76293 — Vao Tsun
– Vao Tsun, Commented Apr 10, 2017 at 11:40

Vao Tsun · Accepted Answer · 2017-04-10 11:43:05Z

57

conditional order can be performed with CASE, like here:

select *
    from test
order by 
    flag
  , case when flag then date end desc
  , case when not flag then date end asc

answered Apr 10, 2017 at 11:43

Vao Tsun

52.7k13 gold badges115 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheRealChx101 Over a year ago

But won't this always order by flag? In my case, I'd like to either make all cases optional or have a default sort field. The cases should be exclusive

Collectives™ on Stack Overflow

PostgreSQL - conditional ordering

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related