how to target records with most recent date.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to target records with most recent date.

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    how to target records with most recent date.

    Hi all, i have a table with many records in it, the records are duplicate apart from the date field. I am trying to get only one record out of the set for each postcode, the one with the most recent date using SQLPlus

    example records:

    EH6 6PQ GB sueha 09/03/2006 00:00
    EH6 6PQ GB sharonc 01/02/2005 00:00
    EH6 6PQ GB THISREC 01/11/2010 00:00
    EH6 6PQ GB sueha 12/06/2006 00:00
    GL20 5BG GB alanc 16/03/2004 00:00
    GL20 5BG GB paulw 16/06/2003 00:00
    GL20 5BG GB THISREC 13/11/2008 00:00
    GL20 5BG GB lync 04/03/2004 00:00


    Here i am only after the record dated 01/11/2010 to be selected for EH6 6PQ and the record dated 13/11/2008 for GL20 5BG.

    Your help is much appreciated.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Since I don't know the column names, I made up column names.
    This is a fairly straight forward query though.

    Code:
    col1  col2  col3  user  mydate
    EH6   6PQ   GB    sueha 09/03/2006 00:00
    
    SELECT col1, col2, col3, mydate
      FROM mytable
     WHERE (col1, col2, col3, mydate) IN
         ( SELECT col1, col2, col3, MAX(mydate)
             FROM mytable
            GROUP BY col1, col2, col3)
     ORDER BY 1,2,3;
    this space intentionally left blank

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Thanks for the reply.. i needed that user name as well though, i did it myself in the end by creating a view and using a join to get what i wanted.

    Thanks again.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width