List:General Discussion« Previous MessageNext Message »
From:Richard Date:April 8 2008 1:17pm
Subject:Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
View as plain text  
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/
>>
>>
> 

Thread
Help with ORDER BY using two colomnsRichard8 Apr
  • Re: Help with ORDER BY using two colomnsKristian Myllymäki8 Apr
    • Re: Help with ORDER BY using two colomnsRichard8 Apr
      • Re: Help with ORDER BY using two colomnsRafael Barbolo Lopes8 Apr
        • Re: Help with ORDER BY using two colomnsRichard8 Apr
          • Re: Help with ORDER BY using two colomnsBen Clewett8 Apr
            • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
              • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Ben Clewett8 Apr
                • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
                  • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Andy Wallace8 Apr
                    • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Richard8 Apr
                  • Re: Help with ORDER BY using two colomns [ solved thankyou :) ]Ben Clewett8 Apr
      • Re: Help with ORDER BY using two colomnsTim McDaniel8 Apr
  • Re: Help with ORDER BY using two colomnsBen Clewett8 Apr