how to target records with most recent date.

[Sureshy]

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.

[gandolf989]

Since I don't know the column names, I made up column names.
This is a fairly straight forward query though.

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;

[Sureshy]

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.