Comparing dates in one field

I have a table with an ID, Date and Comments and need to compare the ID and
the date and retrieve the latest comment for a filing based on the date. So
you could have many records with the same ID that refer to the same filing,
only with a number of different dates. I've tried various things, but do no
t
get the right result.
example:
ID Date Comment
01 6/1/2006 This is the first comment
01 6/2/2006 This is the second comment *
02 6/2/2006 This is a different comment
02 6/5/2006 This is a new comment for this filing *
What I need is to get the second row with the 01 and the last row with 02 ID
in this example indicated by *.
I'm using SQL Server 2000. Thanks for your help in advance.CREATE TABLE #Temp ([ID] VARCHAR(2),
[Date] DATETIME,
[Comment] VARCHAR(2000),
PRIMARY KEY ([ID], [Date]))
INSERT INTO #Temp ([ID], [Date], [Comment])
SELECT '01', '2006-06-01', 'This is the first comment'
UNION SELECT '01', '2006-06-02', 'This is the second comment*'
UNION SELECT '02', '2006-06-02', 'This is a different comment'
UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
UNION SELECT '02', '2006-06-06', 'This is a newer comment'
UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
UNION SELECT '02', '2006-07-01', 'The newest comment*'
SELECT t1.[ID], t1.[Date], t1.[Comment]
FROM #Temp t1
WHERE t1.[Date] =
(
SELECT MAX([Date])
FROM #Temp t2
WHERE t2.[ID] = t1.[ID]
)
GROUP BY t1.[ID], t1.[Date], t1.[Comment]
DROP TABLE #Temp
"SK" <SK@.discussions.microsoft.com> wrote in message
news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>I have a table with an ID, Date and Comments and need to compare the ID and
> the date and retrieve the latest comment for a filing based on the date.
> So
> you could have many records with the same ID that refer to the same
> filing,
> only with a number of different dates. I've tried various things, but do
> not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
> ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Try this..
SELECT ID,Date,Comment FROM
YourTable WHERE Date =
(SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
- Sha Anand
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Hi there
One query that you can try is:
SELECT A.ID, A.Date, A.Comment
FROM dbo.[Comments] A
INNER JOIN (SELECT ID, MAX(Date) FROM dbo.[Comments] GROUP BY ID) B
ON A.ID = B.ID
Lucas
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Thank you very much for your quick response! It seems to work perfectly!
I had the second Where clause in the wrong place!
"Sha Anand" wrote:
> Try this..
> SELECT ID,Date,Comment FROM
> YourTable WHERE Date =
> (SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
> - Sha Anand
>
> "SK" wrote:
>|||Wow! This was quite fast and thorough!
I've never tried it this way before with Union Select. But it works
beautifully.
Thank you Mike for taking the time to go to such length! I Appreciate it!
Have a lovely day!
SK
"Mike C#" wrote:

> CREATE TABLE #Temp ([ID] VARCHAR(2),
> [Date] DATETIME,
> [Comment] VARCHAR(2000),
> PRIMARY KEY ([ID], [Date]))
> INSERT INTO #Temp ([ID], [Date], [Comment])
> SELECT '01', '2006-06-01', 'This is the first comment'
> UNION SELECT '01', '2006-06-02', 'This is the second comment*'
> UNION SELECT '02', '2006-06-02', 'This is a different comment'
> UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
> UNION SELECT '02', '2006-06-06', 'This is a newer comment'
> UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
> UNION SELECT '02', '2006-07-01', 'The newest comment*'
> SELECT t1.[ID], t1.[Date], t1.[Comment]
> FROM #Temp t1
> WHERE t1.[Date] =
> (
> SELECT MAX([Date])
> FROM #Temp t2
> WHERE t2.[ID] = t1.[ID]
> )
> GROUP BY t1.[ID], t1.[Date], t1.[Comment]
> DROP TABLE #Temp
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>
>