List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:April 8 2008 12:43pm
Subject:Re: Help with ORDER BY using two colomns
View as plain text  
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