SQL to have one specific record at the top, all others below

Question

I am trying to put together a query that will display one specific record (found by the record's primary ID) at the top, and display all other records below it, sorted by date (I have "date_added" as one of the fields in the table, in addition to primary ID).

I could do this with a UNION (first select would locate the record I want, and the other select would display all other records), but I'm wondering if is there perhaps a better way?

I'm using Oracle, by the way.

Answer 1

You can do this by sorting by two fields

The first would be an expression that returns 0 if the row is the one you want or 1 if it isn't. Sort will be ascending so you get your preferred record first.

The second sort field would be date_added so the remaining records are sorted in this order.

Afraid I don't know oracle by in sql server it would be something like

select * 
from the_table 
order by (case id when 999 then 0 else 1 end), date_added desc