From: Ben Clewett
Date: April 8 2008 3:28pm
Subject: Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
List-Archive: http://lists.mysql.com/mysql/212146
Message-Id: <47FB8F3B.9060505@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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=kristian@stripped
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>     --     MySQL General Mailing List
>>>>     For list archives: http://lists.mysql.com/mysql
>>>>     To unsubscribe:    
>>>> http://lists.mysql.com/mysql?unsub=barbolo@stripped
>>>>
>>>>
>>>>
>>>>
>>>> -- 
>>>> .:: Rafael Barbolo Lopes ::.
>>>> http://barbolo.polinvencao.com/
>>>
>>>
>>
> 
>