CODEWITHSUNDEEP
sql
riggsmir
riggsmir

Reputation: 21

Selecting max value on subset of data based on other column's value

I'm looking to left join a value from a subset of data from another table, based on a specific value from the first table. Here are example tables:

table1
-----------------
key    date
1    2020-01-02
2    2020-03-02

table2
-----------------
key    date    value
1    2019-12-13    a
1    2019-12-29    b
1    2020-01-14    c
1    2020-02-02    d
2    2019-11-01    e
2    2019-12-02    f
2    2020-04-29    g

Based on the value of date for a specific key value from table1, I want to select the most recent (MAX(date)) from table2, where temp contains all rows for that key value where date is on or before the date from table1.

So, the resulting table would look like this:

key    date    value
1    2020-01-02    b
2    2020-03-02    f

I'm thinking I could use some type of logic that would create temp tables for each key value where temp.date <= table1.date, then select MAX(temp.date) from the temp table and left join the value. For example, the temp table for key = 1 would be:

date    value
1    2019-12-13    a
1    2019-12-29    b

Then it would left join the value b for key = 1, since MAX(date) = 2019-12-29. I'm not sure if this is the right logic to go about my problem; any help would be greatly appreciated!

Upvotes: 1

Views: 736

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use a correlated subquery:

select t1.*,
       (select t2.value
        from table2 t2
        where t2.key = t1.key and t2.date <= t1.date
        order by t2.date desc
        fetch first 1 row only
       ) as value
from table1 t1;

Note that not all databases support the standard fetch first clause. You may need to use limit or select top (1) or something else depending on your database.

Upvotes: 1

Related Questions