-
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.
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|