SQL Server CE joining two tables

I have 2 tables, tb1 is users and tb2 is stats.

I have multiple stats for each user marked with a timestamp

I wanted a query where I could get the latest stat for each user I tried a lot of things but SQL Server CE seems a little bit limited...

Can som开发者_运维技巧eone give me a hint on how to perform it?

You will need to create an inline view that gives the latest timestamp for each user and use that to constrain the query. Try something like the below:

    select tb1.userid, tb1.username, tb2.userid, tb2.deaths, tb2.kills, tb2.headshots,   tb2.totalmatches, tb2.lastsync 
    from tb1 
    inner join tb2 on tb1.1userid = tb2.userid 
    inner join (select tb2.userid, max(timestamp) as latest from tb2 group by tb2.userid) x
    on tb2.timestamp = x.latest

;WITH CTE AS ( select tb1.userid, tb1.username, tb2.userid, tb2.deaths, tb2.kills, tb2.headshots, tb2.totalmatches, tb2.lastsync, ROW_NUMBER() OVER(ORDER BY tb2.timestamp DESC) row from tb1 Inner join tb2 on tb1.1userid = tb2.userid ) SELECT * FROM CTE WHERE row = 1