SQLite Forum

Join tables by nearest older date
Mr. Medcalf’s solution is very transparent and straight-forward and it works.  However, I learned to do that sort of thing more like this:

select *
  from WinEvents
  join GameEvents as Game1
    on GameDate <= WinDate
   and not exists(
        select null 
          from GameEvents Game2
         where Game2.GameDate <= WinDate
           and Game2.GameDate > Game1.GameDate )

Among other reasons, the use of ORDER BY and LIMIT in a subquery is not supported by all SQL engines.  Otoh, I admit that my method is harder to understand.

I think you will have a problem if two games occur on the same date, but maybe you know that won’t ever happen.