Postgres Order by multi columns with a condition

Question

I want to order a Postgres query depending on conditions in the table

    id   sensor_id            alarmTime            clearedTime
415068        3734  2016-04-20 12:25:32    2016-04-27 21:05:44  
415070        3734  2016-04-21 12:25:32                   null
415071        3734  2016-03-22 12:25:32    2016-04-29 21:15:44          
415072        3734  2016-04-23 12:25:32                   null          
415073        3734  2016-04-20 12:25:32    2016-04-22 22:05:45          
415074        3734  2016-02-26 12:25:32    2016-04-27 23:15:24

From the above table I want to initially order by nulls first and alarm time with the remaining rows ordered by cleared time only. to get some thing like this

    id   sensor_id            alarmTime            clearedTime
415070        3734  2016-04-21 12:25:32                   null
415072        3734  2016-04-23 12:25:32                   null  
415068        3734  2016-04-20 12:25:32    2016-04-27 21:05:44  
415073        3734  2016-04-20 12:25:32    2016-04-22 22:05:45          
415071        3734  2016-03-22 12:25:32    2016-04-29 21:15:44          
415074        3734  2016-02-26 12:25:32    2016-04-27 23:15:24

I have tried something like this

select * from table
ORDER BY
case alarm.clearedTime is NOT NULL
when TRUE THEN alarmTime
else clearedTime
end  DESC NULLS FIRST;

So what is the problem? Your order condition isnt clear, please explain with more details — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 18, 2016 at 15:04
Your sample look wrong 415072 shoul be first than 415070 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 18, 2016 at 15:18

Juan Carlos Oropeza · Accepted Answer · 2016-05-18 15:19:53Z

1

TRY

ORDER BY COALESCE(clearedTime, alarmTime)

OR something like this.

ORDER BY CASE 
             WHEN clearedTime IS NULL THEN NULL
                                      ELSE 1 
         END NULLS FIRST,
         alarm.alarmTime DESC,
         alarm.clearedTime DESC -- optional to solve tie between 415068 and 415073

edited May 18, 2016 at 15:19

answered May 18, 2016 at 15:06

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

1 Comment

Juan Carlos Oropeza Over a year ago

well at first wasnt sure what was the logic. Glad that solve it. Remember the upvote.

Gordon Linoff · Accepted Answer · 2016-05-18 15:06:40Z

0

How about this?

order by alarm.clearedTime nulls first, alarm.alarmTime

answered May 18, 2016 at 15:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres Order by multi columns with a condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related