|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Comparing dates in one fieldI 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. 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 Show quoteHide quote "SK" <S*@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. 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 Show quoteHide quote "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" <S*@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 Show quoteHide quote "SK" wrote: > 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. Thank you very much for your quick response! It seems to work perfectly!
I had the second Where clause in the wrong place! Show quoteHide quote "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: > > > 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. 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 Show quoteHide quote "SK" wrote: > 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.  
Other interesting topics
Identity or GUID?
Index Tuning Running Sum Query ? How to add separator blank rows by SQL Query? Identity Columns - Design Question User defined fields via application Access "inserted" / "deleted" from stored procedure Obtain values from different tables How can I update the col value using extended stored procedure Dynamic View |
|||||||||||||||||||||||
