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

Question

Hello all,

I have this SQL statement:

SELECT * FROM converts 
WHERE email='[email protected]' 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

Answer 1

just change the order or the colums in the order statement

SELECT * FROM converts WHERE email='[email protected]' AND status!='1' ORDER BY priority DESC, date ASC

Answer 2

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='[email protected]' 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.

Answer 3

Assuming that you really meant "time between two records is 10 mintues [or less] then I want priority to take over?

then just sort by ten minutes blocks, and then by priority...

Select ...
Order By DateDiff(min, 0, Date) / 10, Priority

you can adjust the 0 value to control where each "tem-minute-block" starts and stops... If MySql does not have a DateDiff function, use whatever expression in MySQL that provides a count of minutes since some reference time...

Answer 4

Another variation would be:

SELECT * FROM converts 
WHERE email='[email protected]' AND status!='1' 
ORDER BY (unix_timestamp(date)/60) - priority

Still not exactly what you required, but pretty close.

Answer 5

If you are using SQL Server 2005 / 2008, the you can accomplish this task with a recursive CTE. Please note I wrote this text in notepad and it hasn't been tested yet. Once I have a chance to test the query results, then I will update the CTE if there is an error or remove this comment altogether.

WITH date_cte

AS

(SELECT *
 , 'sequential_order' = ROW_NUMBER() OVER
  (PARTITION BY email
  ORDER BY date ASC, priority DESC)
FROM converts
WHERE email = '[email protected]'
AND status <> '1')

, recursive_date_cte

AS

(SELECT dc1.*
 , 'sort_level' = 1
FROM date_cte dc1
WHERE sequential_order = 1
UNION
SELECT dc1.*
 , 'sort_level' = CASE
  WHEN DATEDIFF(MINUTE, dc1.date, dc2.date) <= 10 THEN sort_level
  ELSE sort_level + 1 END
LEFT JOIN date_cte dc2
 ON dc1.sequential_order = dc2.sequential_order - 1
WHERE dc1.sequential_order > 1)

SELECT *

FROM recursive_date_cte

ORDER BY sort_level ASC

, priority DESC