Selecting latest date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting latest date

Author

Topic

DaringDon
Starting Member

9 Posts

Posted - 2008-05-08 : 09:41:33

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-08 : 09:53:48

Taking @List as your table you can get the latest date like this

select * from
(select IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate,row_number()over(
partition by MergedDate order by MergedDate
)as rowid from @List
)t where rowid=1

DaringDon
Starting Member

9 Posts

Posted - 2008-05-08 : 10:06:29

Hi. Thank you very much for taking the time to reply to me. Being fairly new to SQL i really dont understand the code youve suggested. Having looked into it further........would this do the same?

  INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
   SELECT DISTINCT TOP 1   
     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
   GROUP BY MH.EntityID
   ORDER BY MH.MergedDate DESC

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-08 : 10:23:33

Oh I thought otherwise.
What I meant was that after you insert the data into your table i.e @List you can use my query to get the latest MergedDate.
But I think your query will work absolutely fine if you want to insert just the latest MergedDate in your table @List.

DaringDon
Starting Member

9 Posts

Posted - 2008-05-08 : 10:43:24

Hello mate.

Thanks again for you help. Ive tried the query i wrote and it does seem to work on the small sample dat ive tried it on.

Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources