CODEWITHSUNDEEP
sql
ms93
ms93

Reputation: 49

get the max(date) value and join 2 tables

Have 2 tables, Table 1 has username, created (timestamp), dim Table 2 has username, dob

Need to join table and get the dim from table 1 with the max(created) for each username. enter image description here

Upvotes: 0

Views: 272

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT t2.username, t1.created, t1.dim,
           ROW_NUMBER() OVER (PARTITION BY t1.username
                              ORDER BY t1.created DESC) rn
    FROM Table2 t2
    INNER JOIN Table1 t1 ON t2.username = t1.username
)

SELECT username, created, dim
FROM cte
WHERE rn = 1;

Upvotes: 1

Related Questions