ordering tickets by older unanswered at the top and new answered at the bottom

Issue

i have a ticket table like this

tickets : id , user_id , title , has_answer(bool 0,1) , created_at 

i want to sort my tickets by new to old (id desc) but i want unanswered tickets at the top

so i can write somethign like

order by has_answer asc , id desc 

this way i get unanswered tickets at the top but they will also be sorted by id asc , i want older unanswered tickets to be shown at the top

basically i want unanswered ticket at top and sorted by id asc and rest of them (answered) at the bottom sorted by id desc

Solution

If id is a numeric data type you can use conditional sorting with a CASE expression:

ORDER BY has_answer, 
         CASE WHEN has_answer THEN -id ELSE id END

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published