From: Andy Wallace Date: April 8 2008 4:25pm Subject: Re: Help with ORDER BY using two colomns [ solved thankyou :) ] List-Archive: http://lists.mysql.com/mysql/212151 Message-Id: <47FB9C87.3040808@cisdata.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 >>>>> > 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 >>>>> > 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=kristian@stripped >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- MySQL General Mailing List >>>>>> For list archives: http://lists.mysql.com/mysql >>>>>> To unsubscribe: >>>>>> http://lists.mysql.com/mysql?unsub=barbolo@stripped >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> .:: Rafael Barbolo Lopes ::. >>>>>> http://barbolo.polinvencao.com/ >>>>> >>>>> >>>> >>> >>> > > -- Andy Wallace - CISData - IDX Slave AIM: acmwallace awallace@stripped