sql unique and join

Question

This is a lovely little bit of SQL which I can't write. :)

what I need to do is this;

select the latest value from the table (CreatedOn), for each user, for a specific StatID. I need to then join the usertable (ssUSER) and select the u.username and I also need to join the detailstatid from the table. I.e. I just need the latest result from this table for each user and then do a join on the user and detailstatid.

SELECT TOP 1000
   [DetailStatUserLogID]
  ,[UserID]
  ,[DetailStatID]
  ,[OverviewID]
  ,[Count]
  ,[StatPercent]
  ,[SpecificDate]
  ,[CreatedOn]
  ,[ModifiedOn]
  ,[Note]
  ,[LoggedDate]
  ,[OverViewGUID]
FROM [StartStop].[dbo].[DetailStatUserLog]

SELECT TOP 1000
   [DetailStatID]
  ,[DetailStatGUID]
  ,[NameOfStat]
  ,[DetailOfStat]
  ,[CreatedByType]
  ,[DataType]
  ,[CreatedByGUID]
  ,[CreatedBy]
  ,[ModifiedOn]
  ,[CreatedOn]
  ,[OverviewID]
FROM [StartStop].[dbo].[DetailStat]

SELECT TOP 1000
   [UserID]
  ,[EmailAddress]
  ,[Authenticated]
  ,[UserName]
FROM [StartStop].[dbo].[ssUsers]

Thanks for any help you might be able to give. :)

Answer 1

I think this should do want you want. It is hard to test without your DDL or data.

Select u.UserName,
       ds.DetailedStat,
       dsl.CreatedOn,
       dsl.DetailStatId,
       dsl.UserId

From 
(
Select d.UserId, d.DetailStatId, Max(d.CreatedOn) as [CreatedOn]
From DetailStatUserLog d
Where d.CreatedOn = (Select Max(CreatedON)
                   From DetailStatUserLog
                   Where UserID = d.UserId
                   And DetailStatId = d.DetailStatId
                   )
Group By d.UserId, d.DetailStatId

)dsl
Join ssUsers u on dsl.UserId = u.UserId
Join detailstat ds on dsl.DetailStatId = ds.DetailStatId

Answer 2

Try:

select /* entered desired fields here */
from (select l.*, 
             row_number() over (partition by [UserID], [DetailStatID]
                                order by [CreatedOn] desc) rn
      from DetailStatUserLog l) ll
join ssUsers u on ll.[UserId] = u.[UserId]
Join DetailStat s on ll.[DetailStatId] = s.[DetailStatId]
where ll.rn=1