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