CODEWITHSUNDEEP
sqloracle-database
Ariod
Ariod

Reputation: 5851

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

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.

Upvotes: 10

Views: 19297

Answers (6)

Don
Don

Reputation: 124

You can sort more than one record to the top using the same technique

999 first 998 second followed by everything else sorted by date

select * 

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

Upvotes: 1

kachar
kachar

Reputation: 2548

SELECT *
FROM `Table`
ORDER BY (`id` = THE_ID) DESC, `date_added` DESC

Upvotes: 0

Michael Dillon
Michael Dillon

Reputation: 32392

The simple way would be to recognise that you want to display two separate things and therefore write to separate straightforward queries. One query to retrieve the first record, and the second to retrieve the sorted list. There is no real performance advantage to doing anything more than this because of one unique record.

Upvotes: -1

Adam
Adam

Reputation: 943

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

Upvotes: 24

Andomar
Andomar

Reputation: 238296

An easier way would be a fancy order by construct. Here's an example for pk = 123:

select *
from YourTable
order by case when yourpk = 123 then 1 else 2 end, date_added

Upvotes: 8

reko_t
reko_t

Reputation: 56450

I don't know Oracle exactly, but you could perhaps do something like..

ORDER BY IF(id == THE_ID, 0, 1), date_added

Upvotes: 2

Related Questions