December 13, 2007 at 8:02 am
ID ShowID DateStamp Title Fname MI Lname FixedBy
------------------------------------------------------------------------------
1 10871.001 10/25/2007 Ms. John V. Jones GP
2 10871.001 10/24/2007 Ms. Michael V. Clark MR
3 10871.001 11/02/2007 Ms. Michael V. Clark LT
4 10871.001 11/04/2007 Ms. John V. Jones AS
From the table above, I need to select all the fields, without any duplicate person, whose datestamp is the most recent. So that after the query is run, it should generate results like below:
ID ShowID DateStamp Title Fname MI Lname FixedBy
------------------------------------------------------------------------------
3 10871.001 11/02/2007 Ms. Michael V. Clark LT
4 10871.001 11/04/2007 Ms. John V. Jones AS
Thanks in advance.
December 13, 2007 at 8:16 am
One solution is to use row_number() with a name based partitioning, like:
SELECT ID, DateStamp, Fname, MI, Lname, FixedBy
FROM ( SELECT ID, DateStamp, Fname, MI, Lname, FixedBy
, row_number() OVER ( PARTITION BY Fname, MI, Lname ORDER BY DateStamp DESC) AS x
FROM mytable
) AS x
WHERE x.x = 1
(this works on 2005 and 2008 only)
Regards,
Andras
December 13, 2007 at 8:28 am
It's beautiful. Thanks for the quick response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply