CODEWITHSUNDEEP
ruby-on-railspostgresql
Blair Anderson
Blair Anderson

Reputation: 20171

PostgreSQL sort by oldest of two dates?

I need to sort a postgres table by the "more recent of column A, fallback to column B"

If my table looks like this: id, reminder_at, updated_at

1, 01-11-2019, 12-01-2018
2, null,       01-04-2019
3, null,       01-02-2019
4, 01-01-2019, 01-04-2019

expected sorting output would be

4, 01-01-2019, 01-04-2019 # 01-01-2019 is soonest 
3, null,       01-02-2019 # then 01-02-2019
2, null,       01-04-2019 # then 01-04-2019
1, 01-11-2019, 12-01-2018 # then 01-11-2019

I'm currently doing this with application code, and i'd prefer to do in SQL

For example if the reminder_at went to NULL for record 1, then it would immediately go to the top because the updated_at date is the oldest

Currently:

SELECT * 
FROM "tasks" 
WHERE completed_at IS NULL
ORDER by reminder_at, updated_at

EDIT with Correct Answer:

SELECT * 
FROM "tasks" 
WHERE completed_at IS NULL
ORDER by COALESCE(reminder_at, updated_at)

Upvotes: 2

Views: 965

Answers (2)

ffejrekaburb
ffejrekaburb

Reputation: 731

I am interpreting your data format to be mm-dd. Should 3 and 2 be flipped in your output, the second coming after the fourth?

Does the following work?

select id,reminder_at,updated_at,greatest(coalesce(reminder_at,'1/1/1900'),coalesce(updated_at,'1/1/1900')) as testcolumn from test order by greatest(coalesce(reminder_at,'1/1/1900'),coalesce(updated_at,'1/1/1900'));

Upvotes: 0

Joe Love
Joe Love

Reputation: 5962

use coalesce. It chooses the first non null value.

 select * from tab
 order by coalesce(col1, col2)

if instead, you wanted to use the greater of the 2 dates.. then use greatest()

 select * from tab
 order by greatest(col1, col2)

Upvotes: 6

Related Questions