Maybe I should rephrase this in the form of a question :) I have a table with workstations and timestamps. I'd like to select the record with the most recent timestamp for each workstation. I know there is a simple query to accomplish this but I'm having difficulty. Does anyone have any suggestions? Am I posting on the correct list to get this answered? Thanks.
Joel Nimety wrote: > The default "host" table can possibly have multiple records for a given > workstation. Presumable the duplicate records are for historical > purposes, but only one is actually current. I don't really care why > there are duplicates but only need to a query to exclude the duplicate > records (leaving the "duplicate" with the most recent date) and still > including the non-duplicated records alone. I think the HAVING clause > is the answer but can't seem to craft the appropriate query. > > > SAMPLE OF VIEW AS IT CURRENTLY EXISTS: > TAG | EI_EntityID (Primary Key) | Machine > Name | Last Activity Date > -------------------------------------------------------------------------------------------------------------------- > ... > CYB1 | 000F20D0272D-453E8A1B-030E-0CE7-0734 | BBLPTP > | Wed Jan 10 10:31:38 EST 2007 > CYB1 | 000F20D0272D-453E8A1B-0556-F357-0744 | > BBREDENBERG | Fri May 05 13:37:29 EDT 2006 > CYB1 | 00508BE168CD-44DA4A44-02A6-4EDB-009E | > CAL_LAPTOP | Wed Aug 09 17:49:13 EDT 2006 > CYB1 | 000F20D0272D-453E8A1B-0365-CEED-02D6 | > CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 > CYB1 | 00508BE171F3-4587E84F-0540-56B8-003D | > CFWRKSTATION | Tue Feb 06 16:14:03 EST 2007 > CYB1 | 000F20D0272D-453E8A1B-00F7-38CE-0323 | > CMILLER1-WIN | Mon Apr 24 10:20:41 EDT 2006 > ... > > Please note the CAL_LAPTOP rows. This is an example of the duplication I > was talking about above. As you can see the top one is older than the > lower one. I need to exclude the older (top) one from the result set > because there is a more current record (lower) with the same TAG and > MACHINE NAME. > > SAMPLE DESIRED RESULT SET: > TAG | EI_EntityID (Primary Key) | Machine > Name | Last Activity Date > -------------------------------------------------------------------------------------------------------------------- > ... > CYB1 | 000F20D0272D-453E8A1B-0556-F357-0744 | > BBREDENBERG | Fri May 05 13:37:29 EDT 2006 > CYB1 | 000F20D0272D-453E8A1B-0365-CEED-02D6 | > CAL_LAPTOP | Fri Feb 09 09:46:09 EST 2007 > CYB1 | 00508BE171F3-4587E84F-0540-56B8-003D | > CFWRKSTATION | Tue Feb 06 16:14:03 EST 2007 > ... > > -- Joel Nimety Product Architect 203.541.3416 [EMAIL PROTECTED] http://www.perimeterusa.com -- The sender of this email subscribes to Perimeter Internetworking's email anti-virus service. This email has been scanned for malicious code and is believed to be virus free. For more information on email security please visit: http://www.perimeterusa.com/email-defense-content.html This communication is confidential, intended only for the named recipient(s) above and may contain trade secrets or other information that is exempt from disclosure under applicable law. Any use, dissemination, distribution or copying of this communication by anyone other than the named recipient(s) is strictly prohibited. If you have received this communication in error, please delete the email and immediately notify our Command Center at 203-541-3444. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
