A modification to my last email, try:
SELECT
*, IF(update != '', update + 10, date) AS o
FROM
my_table
ORDER BY o DESC;
+-----+------+--------+------+
| num | date | update | o |
+-----+------+--------+------+
| 5 | 40 | 90 | 100 |
| 2 | 10 | 60 | 70 |
| 6 | 50 | | 50 |
| 4 | 30 | | 30 |
| 3 | 20 | | 20 |
| 1 | 1 | | 1 |
+-----+------+--------+------+
Richard wrote:
> Thanks,
>
> I think that your solution will be sufficient for my needs, however I
> would still like to know for my personal knowledge how to manage
> correctly this kind of need.
>
> And to make it more complicated I've just rearlised that there is
> another element to take into account, I would need to add 10 days to the
> update dates so they would place themselves in the correct position.
>
> This is how I need the system to work :
>
> Any new requests (without an update value) are ordered by date
> I want to be able to answer these requests (adding a time stamp to the
> update field and if the customer does not answer within 10 days, to re
> insert them into the list.
>
> But as the update timestamp will be 10 days old, I would like to add 10
> days to the update while inserting them to the list (not changing the
> actual value inserted in the database just add 10 days during the
> reordering process.). I hope my explanation in understadable ...
>
> :)
>
> Rafael Barbolo Lopes a écrit :
>> Can't you do Something like:
>>
>> ORDER BY (update,date)
>>
>> The major column of ordering would be update and the second date.
>>
>> I'm not sure about this "solution"
>>
>> On Tue, Apr 8, 2008 at 8:54 AM, Richard <mysql_list@stripped
>> <mailto:mysql_list@stripped>> wrote:
>>
>> Hello I've tried the following with mysql 4.1.11
>>
>> SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
>> '2' AND `update` < '".(time()-864000)."') CASE WHEN `update` = ''
>> THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;
>>
>> It does not work but, is it my code that is wrong or is it just that
>> case does not work with mysql 4.1.11 ?
>>
>> Thanks :)
>>
>> Kristian Myllymäki a écrit :
>>
>> mysql version?
>>
>> http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
>>
>> order by case when updated is not null then updated else created
>> end desc;
>>
>> /Kristian
>>
>> On Tue, Apr 8, 2008 at 1:04 PM, Richard <mysql_list@stripped
>> <mailto:mysql_list@stripped>> wrote:
>>
>> Hello,
>> I've got a table which containes two date colomns.
>> The first one is called `date` and the second `update`
>> In the first one I put the ticket creation date, and on
>> update I add or
>> change the update value.
>> So the update colomn does not contain a value until the
>> first update has
>> been done.
>> I would like to order the tickets by their last update
>> value. And if this
>> value does not exist use the date value.
>>
>> at the moment I use this :
>>
>> ORDER BY `date` DESC"
>> and I would like to replace it by something like this :
>>
>> ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)
>>
>> I know this code is completly wrong, just to try and show
>> you what I need
>> ...
>>
>> Here is an example of what I want to achieve
>>
>> num | date | update
>> -------------------------------------------
>> 1 | 1 |
>> 2 | 10 | 60
>> 3 | 20 |
>> 4 | 30 |
>> 5 | 40 | 90
>> 6 | 50 |
>>
>> The required result would be :
>>
>> num | date | update
>> -------------------------------------------
>> 5 | 40 | 90
>> 2 | 10 | 60
>> 6 | 50 |
>> 4 | 30 |
>> 3 | 20 |
>> 1 | 1 |
>>
>> Thanks in advance :)
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>>
>>
>> -- MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>>
>> --
>> .:: Rafael Barbolo Lopes ::.
>> http://barbolo.polinvencao.com/
>
>
