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
