List:General Discussion« Previous MessageNext Message »
From:Richard Date:April 8 2008 4:46pm
Subject:Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
View as plain text  
Yes that would be easier, except that I would still have to create a 
tempory table to add 10 days onto the ones which have a status waiting 
for answer from customer and have not been answered for more than 10 days.

This system is for customers who do not have an account yet to contact 
me. And will only be used by me an my team. In normal usage I will not 
be expecting the table of unanswered messages to be any longer than 10 
or 20 lines,

So I will leave it be for the moment as it works exactly as I want it to 
and as it will be on a server with alot of free ressources.

Thanks for all your suggestions ! :)

Andy Wallace a écrit :
> 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/
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>
>>
> 

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