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]
