ORDER BY on several conditions

Question

I need a query that will give me a result that will be either (a) the highest price, or (b) those with the oldest timestamp. In all cases, the price must exceed the timestamp (i.e. if the record has a really old timestamp at a higher price than all the others, it should always return the record with the highest price)

Here are some 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

Must return id 3 because it is the highest price

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

In my current request, I am doing this:

SELECT * FROM table ORDER BY price, date DESC LIMIT 1

Unfortunately, this request does not work as I described it above.

thanks for the help

Answer 1

, , , , :

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

Answer 2

, ,

SELECT 
    p.*
FROM table p
WHERE price = (SELECT MAX(price) FROM table)
    OR date = (SELECT MIN(date) FROM table)