Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Richard 
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 <[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]
> 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 :

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Ben Clewett 

Richard,

The query I gave you required the column 'o' to be calculated for each 
row at the time of gathering the data.  When all rows have been 
gathered, the data will be stored and sorted in a temporary table.  This 
temporary table will be in memory or on disk depending on the setting of 
the variable 'tmp_table_size'.  Then the result will be pruned to the 
LIMIT and sent.


You can see that the LIMIT does not help, MySql needs to know what the 
top rows will be, before it discards the bottom ones.  It can't know 
this until all data has been gathered and sorted.


This is all quite a lot of work for your MySql.  Hence it is not 
regarded as scalable.




Alternatively, store the order permanently on the table as a new field 
and give it an index:


ALTER TABLE  ADD o INT NOT NULL,
 ADD KEY(o);

Now the job of MySql is easy, the query just follows the index.  No 
temporary tables, no scanning all rows and no sorting before sending, 
and the LIMIT now works as you would hope.  This is highly scalable.


But you have to maintain the ordering field.  If your brave, do it with 
a trigger :)


Ben


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 <[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]
> 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

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Andy Wallace 

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 <[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]
> 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  |

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Richard 

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] 
> 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]
> 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   |

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Ben Clewett 

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] 
> 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]
> 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.mys

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

2008-04-08 Thread Richard 

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] 
> 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]
> 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]