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-30}, you could just do:

SELECT *
FROM foo
WHERE effective_date = SELECT MAX(effective_date) FROM foo WHERE effective_date < 2013-06-30 

How do you generalize this query when the size of D is greater than 1, assuming D will be specified in an IN clause?

Answer 1

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.

Answer 2

perhaps this can help :

SELECT *
FROM foo
WHERE effective_date IN
(
    (SELECT MAX(effective_date) FROM foo WHERE effective_date < '2013-02-20'),
    (SELECT MAX(effective_date) FROM foo WHERE effective_date < '2013-06-30'),
    (SELECT MAX(effective_date) FROM foo WHERE effective_date < '2013-12-19')
)

result :

---------------------
| id |effective_date|
---------------------
|  1 |    2013-02-03| -- different
|  4 |    2013-05-11|
|  6 |    2013-12-08|

---

UPDATE - 06 December

---

create procedure :

DELIMITER $$

USE `test`$$ /*change database name*/

DROP PROCEDURE IF EXISTS `myList`$$

CREATE PROCEDURE `myList`(ilist VARCHAR(100))
BEGIN
    /*var*/
    /*DECLARE ilist VARCHAR(100) DEFAULT '2013-02-20,2013-06-30,2013-12-19';*/
    DECLARE delimeter VARCHAR(10) DEFAULT ',';
    DECLARE pos INT DEFAULT 0;
    DECLARE item VARCHAR(100) DEFAULT '';

    /*drop temporary table*/
    DROP TABLE IF EXISTS tmpList;

    /*loop*/
    loop_item: LOOP
       SET pos = pos + 1;

       /*split*/
       SET item =
       REPLACE(
           SUBSTRING(SUBSTRING_INDEX(ilist, delimeter, pos),
                LENGTH(SUBSTRING_INDEX(ilist, delimeter, pos -1)) + 1),
       delimeter, '');

       /*break*/
       IF item = '' THEN
           LEAVE loop_item;
       ELSE
           /*create temporary table*/
           CREATE TEMPORARY TABLE IF NOT EXISTS tmpList AS (
                    SELECT item AS sdate
               );
       END  IF;
    END LOOP loop_item;

    /*view*/
    SELECT * FROM tmpList;
END$$

DELIMITER ;

call procedure :

CALL myList('2013-02-20,2013-06-30,2013-12-19');

query :

SELECT
   *,
   (SELECT MAX(effective_date) FROM foo WHERE effective_date < sdate) AS effective_date
FROM tmpList

result :

------------------------------
|    sdate    |effective_date|
------------------------------
| 2013-02-20  |  2013-02-03  | 
| 2013-06-30  |  2013-05-11  |
| 2013-12-19  |  2013-12-08  |