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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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/[EMAIL PROTECTED]
-- MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
