CODEWITHSUNDEEP
sqlsqlite
Luke
Luke

Reputation: 7099

SQL join to row with most recent date

I'm trying to use sqlite to join two tables with dates that don't perfectly match up.

For a given group/date in the left table, I want to join the corresponding record from the right table with the a date just before that of the left table. Probably easiest to show with an example.

table1:

group     date      teacher
  a     1/10/00        1
  a     2/27/00        1
  b     1/7/00         1
  b     4/5/00         1
  c     2/9/00         2
  c     9/12/00        2

table2:

teacher    date    hair length
   1       1/1/00       4
   1       1/5/00       8
   1       1/30/00     20
   1       3/20/00    100
   2       1/1/00       0
   2       8/10/00     50

Gives us:

group     date      teacher    hair length
  a     1/10/00        1           8
  a     2/27/00        1          20
  b     1/7/00         1           8
  b     4/5/00         1         100
  c     2/9/00         2           0
  c     9/12/00        2          50

Upvotes: 0

Views: 1390

Answers (2)

Jaugar Chang
Jaugar Chang

Reputation: 3196

If the hair length will always become longer by time, then you could do this:

select tablea.teachergroup, tablea.date, tablea.teacher,
       max(tableb.hair_length)
from tablea
join tableb
on tablea.teacher=tableb.teacher
and tablea.date>=tableb.date
group by tablea.teachergroup, tablea.date, tablea.teacher

This could work for your sample data, but if hair length could become shorter, you have to :

select a.teachergroup, a.date, a.teacher,
       tableb.hair_length
from (select tablea.teachergroup, tablea.date, tablea.teacher,
       max(tableb.date) as tdate   -- last date before group date
      from tablea
      join tableb
        on tablea.teacher=tableb.teacher
       and tablea.date>=tableb.date
     group by tablea.teachergroup, tablea.date, tablea.teacher
     ) as a
join tableb
on   a.teacher=tableb.teacher
and  a.tdate=tableb.date

These are the efficient way I could do so far.

Upvotes: 1

Denise
Denise

Reputation: 2015

First of all, some sqlite statements for anyone trying to reproduce this problem:

 create table tablea (teachergroup varchar(1), date datetime, teacher int);

 insert into tablea (teachergroup, date, teacher) VALUES 
 ('a','2000-01-10',1),('a','2000-02-27',1),
 ('b','2000-01-07',1),('b','2000-04-05',1),
 ('c','2000-02-09',2),('c','2000-09-12',2);

 create table tableb (teacher int, date datetime, hair_length int);   

 insert into tableb (teacher, date, hair_length) VALUES 
 (1,'2000-01-01',4),(1,'2000-01-05',8),
 (1,'2000-01-30',20),(1,'2000-03-20',100),
 (2,'2000-01-01',0),(2,'2000-08-10',50);

Now, the most straightforward query (though certainly not a very performant one) would join the two tables like this:

 select * 
 from tablea a 
 join tableb on a.teacher=b.teacher 
     and b.date in 
     (select max(date) from tableb where date < a.date and teacher=a.teacher);

Another thing you could do is join the tables a bit more liberally:

select * from a join b on a.teacher=b.teacher and b.date<a.date;

And then use any technique you would ordinarily use for getting the row that corresponds to the maximum date. (Like putting it into a temporary table, then getting the minimum date using a group by and then joining that back to the temporary table to get the row. I'm sure there are other ways.)

Upvotes: 1

Related Questions