May 8, 2008 at 7:43 am
Hello all. I ma using the following query to pull back data. The MergeHistory table has a column named DateMerged. I am looking to pull back the one record with the most recent DateMerged. I have managed to get the query as far as below but not sure how to select the most recent one. Can anyone help with this? I was told it may be along the line of SELECT TOP 1 or something?
INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
SELECT DISTINCT
RT.IndexId,
isnull(dbo.ufn_GetBestIdentifier(RT.IndexId), dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(varchar, RT.DateCreated, 106) as DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM @resulttable AS RT, MergeHistory AS MH
WHERE RT.IndexId = MH.EntityID
May 8, 2008 at 8:11 am
Gavin
Please include the DDL for your table, that helps a person determine how to self join the table and get the results you need.
Thanks
Marvin Dillard
Senior Consultant
Claraview Inc
May 8, 2008 at 8:30 am
You could use top 1 or you could try something like this:
[font="Courier New"]SELECT DISTINCT
RT.IndexId,
ISNULL(dbo.ufn_GetBestIdentifier(RT.IndexId),
dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(VARCHAR, RT.DateCreated, 106) AS DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM
@resulttable AS RT INNER JOIN
MergeHistory AS MH ON
RT.IndexId = MH.EntityID
WHERE
MH.MergeDate = (SELECT MAX(MergeDate) FROM MergeHistory WHERE EntityId = MH.EntityID)[/font]
Note that I also changed your query to use the ANSI standard Join syntax, mainly because I find it easier to read. You also should fully qualify your object names.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply