oracle SQL 在顶部有一个特定的记录，所有其他记录在下面
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1699955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to have one specific record at the top, all others below
提问by Ariod
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).
我正在尝试将一个查询放在一起，该查询将在顶部显示一条特定记录（由记录的主 ID 找到），并显示其下方的所有其他记录，按日期排序（我将“date_ added”作为字段之一）表，以及主 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?
我可以用 UNION 来做到这一点（第一个选择会找到我想要的记录，另一个选择会显示所有其他记录），但我想知道是否有更好的方法？
I'm using Oracle, by the way.
顺便说一下，我正在使用 Oracle。
回答by Adam
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.
第一个将是一个表达式，如果该行是您想要的行，则返回 0，否则返回 1。排序将升序，因此您首先获得首选记录。
The second sort field would be date_added so the remaining records are sorted in this order.
第二个排序字段将是 date_ added，以便其余记录按此顺序排序。
Afraid I don't know oracle by in sql server it would be something like
恐怕我不知道 sql server 中的 oracle 会是这样的
select *
from the_table
order by (case id when 999 then 0 else 1 end), date_added desc
回答by Andomar
An easier way would be a fancy order by construct. Here's an example for pk = 123:
一种更简单的方法是按构造排序。这是 pk = 123 的示例：
select *
from YourTable
order by case when yourpk = 123 then 1 else 2 end, date_added
回答by reko_t
I don't know Oracle exactly, but you could perhaps do something like..
我不完全了解 Oracle，但您也许可以做类似的事情。
ORDER BY IF(id == THE_ID, 0, 1), date_added
回答by Don
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
999 前 998 秒后跟按日期排序的所有其他内容
select *
选择 *
from the_table
order by (case id when 999 then 0 when 998 then 1 else 2 end), date_added desc
回答by kachar
SELECT *
FROM `Table`
ORDER BY (`id` = THE_ID) DESC, `date_added` DESC
回答by Michael Dillon
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.
简单的方法是认识到您想要显示两个单独的内容，因此编写单独的简单查询。一个查询检索第一条记录，第二个查询检索排序列表。由于一个独特的记录，做比这更多的事情没有真正的性能优势。
