Richard,
No problem, glad it works. But note: this is not scalable. If you have
more than a few hundred rows, you may want to think about a better
solution, like storing the order field permanetly and giving it an index :)
Ben
Richard wrote:
> Thanks, it works like a charm :)
>
> Ben Clewett a écrit :
>> 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/
>>>
>>>
>>
>
>
