Help with ORDER BY
I currently have a query that organizes search results for volunteers that should be called for projects based on how close they live to a project the and there past attendance. Currently doing SELECT name, city, state, phone, prods_done, cancels, miles FROM volunteer_search WHERE project_id = 5653 ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC; The results look something like this: Jim Barnes Chicago, IL 773-555- 2 1 11.5 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 I am trying to find a way to give more weight to reliability (projects done and cancels). For example the fact that Kim Ayers has done 22 projects makes her more desirable than either Jim Barnes and Kelley Smith even though she has 1 cancel is farther from the project than both the other candidates. The desired order would be: Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Jim Barnes Chicago, IL 773-555- 2 1 11.5 Can the query could be modified to take into account such considerations? I realize that I have not really quantified how much reliability and the number of projects done trumps distance from the project, but in the beginning I am willing to set that somewhat arbitrary and adjust it later. I realize that this may be beyond the scope of a MySQL query. If so I am grateful for any ideas on where to go for info/help. Thanks, Richard
RE: Help with ORDER BY
SELECT name, city, state, phone, prods_done, cancels, miles FROM (SELECT name, city, state, phone, prods_done, cancels, miles, ((prod_done - cancels) * 100 / prod_done) reliability FROM volunteer_search WHERE project_id = 5653) A ORDER BY reliability DESC, miles ASC Give it a try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, February 07, 2011 1:08 PM To: mysql@lists.mysql.com Subject: Help with ORDER BY I currently have a query that organizes search results for volunteers that should be called for projects based on how close they live to a project the and there past attendance. Currently doing SELECT name, city, state, phone, prods_done, cancels, miles FROM volunteer_search WHERE project_id = 5653 ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC; The results look something like this: Jim Barnes Chicago, IL 773-555- 2 1 11.5 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 I am trying to find a way to give more weight to reliability (projects done and cancels). For example the fact that Kim Ayers has done 22 projects makes her more desirable than either Jim Barnes and Kelley Smith even though she has 1 cancel is farther from the project than both the other candidates. The desired order would be: Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Jim Barnes Chicago, IL 773-555- 2 1 11.5 Can the query could be modified to take into account such considerations? I realize that I have not really quantified how much reliability and the number of projects done trumps distance from the project, but in the beginning I am willing to set that somewhat arbitrary and adjust it later. I realize that this may be beyond the scope of a MySQL query. If so I am grateful for any ideas on where to go for info/help. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with ORDER BY using two colomns
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]
Re: Help with ORDER BY using two colomns
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]
Re: Help with ORDER BY using two colomns
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]
Re: Help with ORDER BY using two colomns
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/
Re: Help with ORDER BY using two colomns
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]
Re: Help with ORDER BY using two colomns
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]
Re: Help with ORDER BY using two colomns
I think the easiest is to create a new logical column with the correct ordering, something like: SELECT *, IF(update != '', update, date) AS o FROM my_table ORDER BY o DESC; I note that both 'update' and 'date' are reserved works :) Also worth noting that this cannot be assigned an index and is therefore only good for small amounts of data. - If you have lots of data then you need a better solution. Set 'update' to NULL where there is no value and add the key: KEY(update,date) Then order by this key: ORDER BY update DESC, date DESC. Which when I try it gives: +-++--+ | num | date | update | +-++--+ | 5 | 40 | 90 | | 2 | 10 | 60 | | 6 | 50 | NULL | | 4 | 30 | NULL | | 3 | 20 | NULL | | 1 | 1 | NULL | +-++--+ Ben 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 :) * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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
Re: Help with ORDER BY using two colomns
On Tue, 8 Apr 2008, Richard [EMAIL PROTECTED] wrote: 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; 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 ? If you go to that URL, it's a section of the manual for MySQL 5.0. However, a feature of their on-line manuals, incredibly clever and useful for those of us with older or multiple versions, is near the upper left: * MySQL 6.0 Reference Manual * MySQL 5.1 Reference Manual * MySQL 5.0 Reference Manual * MySQL 3.23/4.0/4.1 Manual What's so incredibly clever and useful is that it takes you to *the corresponding section of that version's manual*, if it exists. However, in this case the MySQL 3.23/4.0/4.1 Manual link points to the 5.0 page, which is, I think, how it indicates that a feature was not in a previous version. And, indeed, a bit of Googling says MySQL 5.0 finally introduces functionality for Stored Procedures. HOWEVER. Kristian Myllym?ki gave the wrong URL. That was for the CASE *statement in stored procedures*. As it says near the bottom, The syntax of the CASE *statement* used inside stored routines differs slightly from that of the SQL CASE *expression* described in Section 11.3, Control Flow Functions. The CASE expression is what you can use in a WHERE, et cetera. The last bit is a link. If you follow it, to http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html, it describes the CASE expression for 5.0, and the link at the top points to the slightly different 4.1 version, http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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] 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
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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] 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.
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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] 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
help with ORDER BY
Hi all! I need some help with ORDER BY in the following example. I want to order by selected category, then by subcategories of the selected category, then by categories with the same parent_id of the selected category, then by random if possible, or random within the categories if possible, but the first order by part is not working because is not returning products of the selected category first, instead returns products from a top category (parent_id = 0). table categories id | parent_id | category - where parent_id is 0 for top categories. table products id | id_category | product SELECT products.id, (SELECT CASE WHEN CHAR_LENGTH(products.product) 40 THEN CONCAT(SUBSTRING(products.product,1,37),'...') ELSE products.product END) AS product, (SELECT CASE WHEN CHAR_LENGTH(products.description) 70 THEN CONCAT(SUBSTRING(products.description,1,67),'...') ELSE products.description END) AS description FROM products WHERE products.id_stock = 1 ORDER BY products.id_category IN (.$id_selected_category. , (SELECT categories.id FROM categories WHERE categories.parent_id = .$id_selected_category.) , (SELECT categories.id FROM categories WHERE categories.parent_id = .$parent_id.)) , RAND() LIMIT 11 (the php vars have correct values) Please apologise my bad English. Thanks in advance. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with ORDER BY
Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE I have a bit of a problem that occurs only when i change my really simple log table to the archive engine. The replication breaks. Any thoughts? The row number of the error is variable. When the table is set to myisam, the replication does not break on duplicate key, and runs as expected. Can't write; duplicate key in table 'clog'' on query. Is it possible that due to the stress of the benchmark, my slave cannot compute the next cID or creates a duplicate (cId is the only variable that changes, on bench query)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with ORDER BY
Thing 1: your auto_increment key MUST be your primary key. Thing 2: the timestamp field will be updated with the current epochal timestamp which only increments every second.. as you have a timestamp field as you primary (and therefore unique) key, you will never be able to perform more than one INSERT/UPDATE within the span of any given second. you need to redign the table, I'm afraid. On 9/11/07, WiNK / Rob [EMAIL PROTECTED] wrote: Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE I have a bit of a problem that occurs only when i change my really simple log table to the archive engine. The replication breaks. Any thoughts? The row number of the error is variable. When the table is set to myisam, the replication does not break on duplicate key, and runs as expected. Can't write; duplicate key in table 'clog'' on query. Is it possible that due to the stress of the benchmark, my slave cannot compute the next cID or creates a duplicate (cId is the only variable that changes, on bench query)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help!!!!!!Re: order by...group by...complicated query
hi, i am trying to build a message board in php with mysql as back end; what i want to do is to query the database in a certain way that the result is returned in the correct order for php to display the discussion thread properly here is my table structure: mysql show fields from posts - ; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | msgid | bigint(20) unsigned | | PRI | NULL| auto_increment | | dateadded | timestamp(14) | YES | | NULL|| | subject | mediumblob | | | || | body| longblob| YES | | NULL|| | replytopost | bigint(20) unsigned | YES | | 0 || | aposition | tinyint(3) unsigned | YES | | 0 || | thrid | bigint(20) unsigned | | MUL | 0 || | usrid | bigint(20) unsigned | | MUL | 0 || | username| varchar(50) | | | || +-+-+--+-+-++ 9 rows in set (0.00 sec) the query should look like : select * from posts where thrid = 'the id of the thread' order by.(this is the part i couldn't figure out); the position of a single message into the tree is determined by the following criteria: dateadded : the date in which it was added replytopost: the post to which it belongs, if none then 0 is assigned aposition: the absolute position of the message (horizontal) a combination of these columns in an order by clause should (in theory) build the tree correctly please advise me if i am doing something wrong here, i have tried many combinations without success regards; hassan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
