MySQL 多个条件下的 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9367740/
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
ORDER BY on multiple conditions
提问by rob melino
I need a query that will give me the result that is either (a) the highest price or (b) the one with the oldest timestamp. In all cases, price should trump timestamp (ie if a record has a really old timestamp by higher price than all others, it should always return the record with the highest price)
我需要一个查询，该查询将为我提供 (a) 最高价格或 (b) 时间戳最旧的结果。在所有情况下，价格应该胜过时间戳（即，如果一条记录的时间戳确实比所有其他记录都高，则它应该始终返回价格最高的记录）
Here are a few scenarios:
这里有几个场景：
id | price | date
1 | 5 | 2012-02-20 08:59:06
2 | 5 | 2012-02-20 09:59:06
3 | 7 | 2012-02-20 10:59:06
Should return id 3 because it is highest price
应该返回 id 3 因为它是最高价格
id | price | date
1 | 5 | 2012-02-20 08:59:06
2 | 5 | 2012-02-20 09:59:06
3 | 5 | 2012-02-20 10:59:06
should return id 1 since it is the oldest
应该返回 id 1，因为它是最旧的
In my current query i am doing this:
在我当前的查询中，我正在这样做：
SELECT * FROM table ORDER BY price, date DESC LIMIT 1
Unfortunately this query is not working how i've outlined it above.
不幸的是，这个查询不起作用，我在上面概述了它。
Thanks for your help
谢谢你的帮助
回答by Simon at My School Portal
I'm having trouble determining precisely what you are after, however it sounds like you are looking for the oldest timestamp for the highest price, and so the following should suffice
我无法准确确定您想要什么，但是听起来您正在寻找价格最高的最旧时间戳，因此以下内容就足够了
SELECT *
FROM table
ORDER BY
price DESC, // Favour the highest price
date ASC // Now get the one with oldest date at this price
LIMIT 1
回答by silly
i hope i understand your question, try this
我希望我明白你的问题，试试这个
SELECT
p.*
FROM table p
WHERE price = (SELECT MAX(price) FROM table)
OR date = (SELECT MIN(date) FROM table)
