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
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
WHERE (col1, col2, col3, mydate) IN
( SELECT col1, col2, col3, MAX(mydate)
GROUP BY col1, col2, col3)
ORDER BY 1,2,3;