Abs Abs - 4 months ago 6
MySQL Question

SQL, Order By - How to give more authority to other columns?

I have this SQL statement:

SELECT * FROM converts
WHERE email='myemail@googlemail.com' AND status!='1'
ORDER BY date ASC, priority DESC

This just orders by date but I want to give my column "priority" more authority. How can I do this?

It should order by date first but if the time between two records is 10 mintues then I want priority to take over. How can I do this in my SQL statement or does this have to be in my application logic? I was hoping I could do it in my SQL statement.

Thank you all for any help


You could quantize the 'date' ordering into 10 minute chunks, so how about ordering by floor(unix_timestamp(date)/600), and then by priority

SELECT * FROM converts 
WHERE email='myemail@googlemail.com' AND status!='1' 
ORDER BY floor(unix_timestamp(date)/600) ASC, priority DESC

Though two dates can be still be less than 10 mins apart but straddle two different 10 minute "chunks". Maybe that is sufficient, but I think to do exactly what you request is better done by the application.

(OP requested expanded explanation....)

Take two times which straddle a ten minute boundary, like 9:09 and 9:11 today:

  • floor(unix_timestamp('2009-03-16 09:09:00')/600) = 2061990
  • floor(unix_timestamp('2009-03-16 09:11:00')/600) = 2061991

Suppose you had a higher priority row for 09:11 than 09:09 - it will still appear after the 09:09 row because it fell into the next 10 minute chunk, even though it was only 2 minutes different.

So this approach is an approximation, but doesn't solve the problem as originally stated.

The way you stated your problem, a high priority row could appear before one recorded several hours (or days, or months!) earlier, as long there was an unbroken series of lower priority row with an interval less than 10 minutes.