Return rows with maximum date less than each value in a set of dates in SQL

Question

Consider the following table: ``` CREATE TABLE foo ( id INT PRIMARY KEY, effective_date DATETIME NOT NULL UNIQUE ) ``` Given a set of dates D, how do you fetch all rows from foo whose effective_date is the greatest value less than each date in D in a single query? For simplicity, assume that each date will have exactly one matching row. Suppose foo has the following rows. ``` --------------------- | id |effective_date| --------------------- | 0 | 2013-01-07| --------------------- | 1 | 2013-02-03| --------------------- | 2 | 2013-04-19| --------------------- | 3 | 2013-04-20| --------------------- | 4 | 2013-05-11| --------------------- | 5 | 2013-06-30| --------------------- | 6 | 2013-12-08| --------------------- ``` If you were given D = {2013-02-20, 2013-06-30, 2013-12-19}, the query should return the following: ``` --------------------- | id |effective_date| --------------------- | 1 | 2013-02-03| | 4 | 2013-05-11| | 6 | 2013-12-08| ``` If D had only one element, say D = {2013-06…

Accepted Answer

Actually, your problem is - that you have a list of values, which will be treated in MySQL as row - and not as a set - in most cases. That is - one of possible solutions is to generate your set properly in application so it will look like:

```
SELECT '2013-02-20'
UNION ALL
  SELECT '2013-06-30'
UNION ALL
  SELECT '2013-12-19'
```

-and then use produced set inside `JOIN`. Also, that will be great, if MySQL could accept static list in `ANY` subqueries - like for `IN` keyword, but it can't. `ANY` also expects rows set, not list (which will be treated as row with `N` columns, where `N` is count of items in your list).

Fortunately, in your particular case your issue has important restriction: there could be no more items in list, than rows in your `foo` table (it makes no sense otherwise). So you can dynamically build that list, and then use it like:

```
SELECT 
  foo.*, 
  final.period 
FROM 
  (SELECT 
    period, 
    MAX(foo.effective_date) AS max_date 
  FROM 
    (SELECT 
      period 
    FROM 
      (SELECT 
        ELT(@i:=@i+1, '2013-02-20', '2013-06-30', '2013-12-19') AS period 
      FROM 
        foo 
          CROSS JOIN (SELECT @i:=0) AS init) AS dates 
      WHERE period IS NOT NULL) AS list 
      LEFT JOIN foo 
        ON foo.effective_date<list.period 
    GROUP BY period) AS final 
    LEFT JOIN foo 
      ON final.max_date=foo.effective_date
```

-your list will be automatically iterated via `ELT()`, so you can pass it directly to query without any additional restructuring. Note, that this method, however, will iterate through all `foo` records to produce row set, so it will work - but doing the stuff in application may be more useful in terms of performance.

The demo for your table can be found here.

Return rows with maximum date less than each value in a set of dates in SQL

Solution

Problem

Related problems