|
|
Posted by David Portas on 05/18/06 23:39
Verticon:: wrote:
> I have a table that has records layed out as so:
>
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
>
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
>
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
>
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005
First add the constraint that you're apparently missing:
ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);
Then:
SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|