Hi,
I'm confused why the following doesn't work as expected and what I might be doing wrong/misunderstanding.
-- queries to set up tables r & t
create table r (ritm text);
create table t (task text, ritm text, ud date);
insert into r values ('r1'), ('r2'), ('r3');
insert into t values ('t1', 'r1', '2021-01-01'), ('t2', 'r1', '2021-01-02'), ('t3', 'r1', '2021-01-03');
insert into t values ('t4', 'r2', '2021-01-01');
-- query in question
select * from r
left outer join t on r.ritm =
(select ritm from t where t.ritm = r.ritm order by date(ud) desc limit 1);
what I get:
r1|t1|r1|2021-01-01
r1|t2|r1|2021-01-02
r1|t3|r1|2021-01-03
r2|t4|r2|2021-01-01
r3|||
what I was hoping for:
r1|t3|r1|2021-01-03
r2|t4|r2|2021-01-01
r3|||
Any ideas? My hope was to mimic an outer apply in sqlserver