List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:April 8 2008 4:25pm
Subject:Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
View as plain text  
Not sure, but perhaps an even simpler method would be to consider the
initial insert an update as well... so the update column would always have
a value. Then the sort would (I believe) always be in the order you want,
and if you need to differentiate between rows that are new vs rows that
are updated, (date = update) => new. You can put an index on this field
and not have the performance issue to worry about.

Just a thought.

andy

Richard wrote:
> Thanks,
> 
> This is for the unanswered list of questions, so the output list (not 
> the list stored in the mysql database) should never go over 100.
> 
> by scalable, do you mean alot of ressources being used or a long wait 
> for the answer? Because I belive I Could just use a simple limit if I 
> needed to have a limited number of results on one page.
> 
> Every time a question is answered the update date will change, and the 
> status could also change. So I don't see how to easily do this by 
> creating another table.
> 
> Ben Clewett a écrit :
>> 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/
>>>>>
>>>>>
>>>>
>>>
>>>
> 
> 

-- 
Andy Wallace - CISData - IDX Slave
AIM: acmwallace   awallace@stripped
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