MySQL - Sort on a calculated value based on two dates

Question

I have the following problem that needs to be solved in a MySQL query:

Fields
info - textfield
date1 - a date field
date2 - a date field
offset1 - a text field with a number in the first two positions, example "10-High"
offset2 - a text field with a number in the first two positions, example "10-High"

I need to sort the records by the calculated "sortvalue" based on the current date (today):

If today<date1 then sortvalue=offset1*10+offset2*5-1000
else if today>=date2 then sortvalue=offset1*10+offset2*5+1000
else sortvalue=offset1*10+offset2*5

I have quite good understanding of basic SQL with joins etc, but this I am not even sure if its possible...if it helps I could perhaps live with a single formula giving the same sort of effect as the IFs do....ie. before date1 = low value, after date2 = high value...

Rgds PM

Answer 1

You want a SQL "CASE" clause. Look at http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case

Answer 2

If you add your custom sort value to the select list then you can directly use it in the ORDER BY clause. Something like:

SELECT  field1, field2, fieldn, 
    IF(today < @date1, offset1 * 10 + offset2 * 5 - 1000, 
        IF(today >= @date2, offset1 * 10 + offset2 * 5 + 1000, 
            offset1 * 10 + offset2 * 5
        )
    ) AS sortvalue
ORDER BY sortvalue;

Answer 3

Use:

         ...
    FROM FIELDS t
ORDER BY CASE
           WHEN NOW() < t.date1 THEN CAST(LEFT(offset1, 2) AS INT) * 10 + CAST(LEFT(offset2, 2) AS INT) * 5 - 1000
           WHEN NOW() >= t.date2 THEN CAST(LEFT(offset1, 2) AS INT) * 10 + CAST(LEFT(offset2, 2) * 5 + 1000
           ELSE offset1 * 10 + offset2 * 5
         END