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

Question

Hey all,

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

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

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

I have the same problem as you do.

I would like to have a query for ASP Classic to be applied on an Access database.

1. Imagine that we have a table with the name of "Country".
2. Imagine that we have a column in "Country" that has this name: "CityMajor"
3. Imagine that we wish to have a query sort the items in this column, but with one exception. For example, we do a search first. The resulted item should be placed on top of the resulted query.
4. An example could clarify this point. For example, we have the list of the following Major Cities in our list:

Paris London Tehran Rome New York Berlin Tokyo

1. Now we wish to have this list returned and sorted alphabetically, but with the exception that "New York" stands on top of the other cities. But the other items should be in alphabetical order:

New York Berline London Paris Rome Tehran Tokyo

How can I accomplish this query in ASP Classic for an Access Database?

Thank you in advance for any help in this regard.