Fetching latest item from a relative collection

I got two tables, User and UserActivity.

How do I write a SQL query which fetches each user and it's latest activity? UserActivity.UserId references User.Id.

Might sound simple but I c开发者_C百科an't figure out how to get the latest entry from UserActivity for each user.

try this

Select u.*
       ua.*
from   user u
join   useractivity ua on ua.userid = u.userid
join   (select userid, max(useractivityid) from useractivity groupy by userid) um
       on um.useractivityid = ua.useractivityid

Let's supose that your tables are:

UserT( Id, name )
UserActivity( UserId, sessionNumber, activityTimeStamp)

And when you say latest activity you are talking about the last moment that this user has activity.

In this case, the query is:

select 
   UserT.name, 
   max( activityTimeStamp ) as latestActivity
from
   UserT left outer join
   UserActivity UA on UA.UserId = UserT.Id
group by
   UserT.Id, UserT.name

Yes, is a simple query. Only complexity is grouping by users and get aggregated max time.

Regards and sorry about answer delay. I have a little lag today ;)

If you are talking about all columns of activity, then use CTE:

;with cte as (
  select 
   UA.*,
   ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY activityTimeStamp DESC) as RN,
  from 
   UserActivity UA )
select
    UserT.*, 
    cte.*
from
    UserT left outer join
    cte on cte.RN = 1 and cte.UserId = UserT.Id