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

Question

I have this SQL statement:

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

These are only orders by date, but I want my priority column to have more authority. How can i do this?

First, it must be ordered by date, but if the time between two records is 10 minutes, then I want the priority to take over. How can I do this in my SQL statement or should it be in my application logic? I was hoping I could do this in my SQL statement.

Thank you all for your help.

Answer 1

"" 10- , (unix_timestamp (date)/600),

SELECT * FROM converts 
WHERE email='[e-mail]' AND status!='1' 
ORDER BY floor(unix_timestamp(date)/600) ASC, priority DESC

, 10 , 10- "". , , , , , .

( ....)

, , 9:09 9:11:

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

, 09:11, 09:09 - ​​ 09:09, 10- , 2 .

, , , .

, , ( !) , 10 .

Answer 2

:

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

, , .

Answer 3

, " - 10 [ ], , ?

, ...

...
Order By DateDiff (., 0, )/10,

0 , "--"... MySql DateDiff, MySQL, ...

Answer 4

SQL Server 2005/2008, CTE. , , . , CTE, .

WITH date_cte

AS

(SELECT *
    , 'sequential_order' = ROW_NUMBER() OVER
        (PARTITION BY email
        ORDER BY date ASC, priority DESC)
FROM converts
WHERE email = '[e-mail]'
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

Answer 5

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