TSQL Order By Specific Value

Question

I need to order my results such that all items with the status column being a specific value come up first, then by date.

I tried this:

SELECT Id, Status, CreatedAt FROM Table
ORDER BY (Status=1) DESC, CreatedAt

I figured I'd get a bool value on (Status=1) so ordering by DESC to put the true (1) values on the top.

But I'm getting a syntax error. Is this possible and if so what is the correct syntax?

Thanks!

Answer 1

You can use CASE also in the ORDER BY:

SELECT Id, Status, CreatedAt 
FROM Table
ORDER BY 
   CASE WHEN Status = 1 THEN 0 ELSE 1 END ASC, 
   CreatedAt ASC

Answer 2

Try this

SELECT Id, Status, CreatedAt FROM Table
ORDER BY (case when Status=1 then 1 else 2 end), CreatedAt