CODEWITHSUNDEEP
sqlpostgresql
Marek M.
Marek M.

Reputation: 3951

Ordering by two columns

Let's say I have a table that looks like this:

enter image description here

As you can see - row with id 6 is a response to row with id 3. I'd like to order my results set so that it would appear between rows 3 and 7. How to accomplish that? Doing: select * from comments_comment order by updated_at, response_to_id gets me exactly what you can see on the image.

Upvotes: 4

Views: 54

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

ORDER BY COALESCE(response_to_id, id), id

The logic behind your ordering is that you want parent records, and their children, to appear grouped together, with the parent appearing on top of the group.

The above ORDER BY is that is it identifies the group id by taking the response_to_id in the case of children, or the actual id in the case of a parent where the response_to_id is null. Then within each group it orders by the id value.

Upvotes: 4

Related Questions