Find latest matching item in another table

Question

Consider the following two tables, named tbl_users and tbl_actions respectively:

user_id     user_name     capture_date
u123        John Smith       2016-04-01
u124        Hannah Montana   2015-10-07
u125        James Bond       2014-07-28


user_id     user_alias      use_date
u123         Jonathan Smith  2012-01-01
u125         Double-O Seven  2012-02-04
u125         O-O-Seven       2015-11-25

I want to join them such that I would find the "latest used" alias of each user that is before their capture date. So the expected output is:

user_id     user_name     capture_date   latest_alias_by_then
u123        John Smith       2016-04-01     Jonathan Smith
u124        Hannah Montana   2015-10-07
u125        James Bond       2014-07-28     Double-O Seven

How do you do that efficiently in SQL? I am using Microsoft SQL Server so functions like OVER and RANK are available too.

Possible duplicate of [SQL join: selecting the last records in a one-to-many relationship](http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship) — Tab Alleman, Apr 27 '16 at 19:49

score 2 · Accepted Answer · answered Apr 27 '16 at 19:12

SELECT  *
FROM    (
        SELECT  u.*, a.user_alias,
                ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY a.use_date DESC) rn
        FROM    tbl_users u
        LEFT JOIN
                tbl_actions a
        ON      a.user_id = u.user_id
                AND a.use_date <= u.capture_date
        ) q
WHERE   rn = 1

or

SELECT  *
FROM    tbl_users u
OUTER APPLY
        (
        SELECT  TOP 1
                user_alias
        FROM    tbl_actions a
        WHERE   a.user_id = u.user_id
                AND a.use_date <= u.capture_date
        ORDER BY
                use_date DESC
        ) q

Create an index on tbl_actions (user_id, use_date) for the queries to work faster.

Either of these methods can be more efficient than the other, depending on how many users and how many actions per user you have.

Find latest matching item in another table

1 Answers1