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]
