RE: I can't figure out what I thought would be a simple query..
Okay - I can't figure this out as a single sql statement. However Assuming there aren't likely to be duplicate rows you could do: create table temp2 (table definition) select max(endtime), non_unique_id temp1 group by non_unique_id; then you could select a.endtime, a.need_id, b.unique_id from table temp1 a, temp2 b where a.endtime=b.endtime and a.unique_id=b.unique_id; If there are duplicates I suspect you'd get away with distinct? Of course I'm sure there's a smarter way! Rgds Mike (--traitorous Oracle Ingres DBA) -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 22:30 To: Jim Matzdorff; MySQL List Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list
I can't figure out what I thought would be a simple query..
All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I can't figure out what I thought would be a simple query..
I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- 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: I can't figure out what I thought would be a simple query..
Hi guys, Have you seen the manual page for The Rows Holding the Group-wise Maximum of a Certain Field: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html I think that's what you want to do. You can either use another temporay table, the MAX-CONCAT trick, or the LEFT JOIN ... IS NULL trick from the comment on March 16, 2003. In MySQL 4.1, you could also use a subselect. Keep in mind with the LEFT JOIN ... IS NULL trick, the more duplicate values you have on your non-unique column, the more inefficient it will be. However, it seems like the subselect method would have this problem also. Can someone tell me if this is true or am I thinking wrong? Hmm. Hope that helps. Matt - Original Message - From: Larry Brown Sent: Monday, October 27, 2003 4:29 PM Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff Sent: Monday, October 27, 2003 4:37 PM Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
