Conditions on latest record

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/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||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/...US,SQL.90).aspx
--sqlsql