SQL Order By: Specific Value First, then Ordering?

Question

I have a select statement that needs to order the results by two columns. My problem is that I only need the ordering to apply on the first column when there is a specific value. Otherwise, I want the results to be sorted by the second column.

I'm sure my explanation confused most people, so here's an example: In most cases I want the results to be sorted by Column B (Date) DESC. However, there are situations where I need records to be shown above (or before) the Date ordering. This occurs when Column A (Status) has the value 'Needs Response'. So, if Status = 'Needs Response' then I need it at the top of the results, but, if it has any other value, it should only then be ordered by Date in DESC order.

In my attempts to accomplish this I worked with CASE statements but every attempt results in all of the results being ordered by Status and then by Date. What I need is the initial order by to apply only when the Status value = 'Needs Response'.

Status         |  Date
---------------------------
Done           |  11/25/2018
Done           |  11/12/2018
Confirm        |  10/10/2018
Needs Response |  12/5/2018
Acknowledge    |  12/8/2018
Confirm        |  12/1/2018
Needs Response |  11/24/2018
Confirm        |  12/10/2018

Using the same data above, no matter how I attempt this I always first get the data sorted by Status and then by Date. When using a CASE statement I am able to get 'Needs Response' to the top of the results, but the remaining records are still sorted by Status before Date.

My desired outcome of the sample data would be:

Status         |  Date
---------------------------
Needs Response |  12/5/2018
Needs Response |  11/24/2018
Confirm        |  12/10/2018
Acknowledge    |  12/8/2018
Confirm        |  12/1/2018
Done           |  11/25/2018
Done           |  11/12/2018
Confirm        |  10/10/2018

Answer 1

You should be able to manage this with a ORDER BY clause like :

ORDER BY 
    CASE WHEN Status = 'Needs Response' THEN 0 ELSE 1 END,
    Date desc

So basically you would sort with two fields :

• first, a computed value that will be 0 when Status has value Needs Response ; in all other cases it should show 1. This will allow the Need Response status to appear first, while all other values will have an equal value, causing the following sort field come into play

• then Date

Answer 2

Use a case expression to put Needs Response rows first. Then order by date descending:

order by case when status = 'Needs Response' then 0 else 1 end, date desc