ORDER BY on multiple conditions

Question

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)

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 | 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 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

Answer 1

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