0

Given a members table, having the following columns:

  accepted_at datetime
, updated_at  datetime NOT NULL

Given this query:

select * from members
order by accepted_at DESC NULLS FIRST, updated_at DESC

We invariably always want accepted_at IS NULL at the beginning of the list, then the most recent members updated following in the list.
The above query prioritizes on the updated_at column, so I end up with accepted members at the beginning of list.

3
  • Sorry, not sure I understood the actual requirement. When accepted_at is null, you want all those at the top and sorted by updated_at DESC; when accepted_at is not null, you want to disregard actual accepted_at and just sort by updated_at DESC. Is that correct?
    – Andriy M
    Feb 8, 2023 at 12:36
  • i'll try to simplfy : members who did not accepted (accepted_at is null) should always be first (without any further order requirements). Then the others (accepted_at is not null) should be sorted by updated_at
    – Ben
    Feb 8, 2023 at 15:05
  • Thanks. I think my answer does exactly that. If it doesn't, please let me know.
    – Andriy M
    Feb 8, 2023 at 17:34

2 Answers 2

2

My guess is you want something like this:

ORDER BY
  (accepted_at IS NOT NULL) ASC  -- or: (accepted_at IS NULL) DESC – same effect
, updated_at DESC
-1

When encountering similar task, the following syntax worked for me:

select * from members
order by accepted_at NULLS FIRST, updated_at DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.