CODEWITHSUNDEEP
sqlsql-serversql-server-ce
Killercode
Killercode

Reputation: 904

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 someone give me a hint on how to perform it?

Upvotes: 0

Views: 2726

Answers (2)

Nausif
Nausif

Reputation: 400

;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

Upvotes: 1

Adrian
Adrian

Reputation: 207

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

Upvotes: 1

Related Questions