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

Question

Consider the following table:

CREATE TABLE foo ( id INT PRIMARY KEY, effective_date DATETIME NOT NULL UNIQUE ) 

Given a set of D dates, how do you extract all rows from foo whose effective_date is the largest value less than every date in D in a single query?

For simplicity, suppose that each date has exactly one matching string.

Suppose foo has the following lines.

 --------------------- | 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 request 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 if D is specified in the IN clause?

Answer 1

Actually, your problem is that you have a list of values ​​that will be processed in MySQL as a string, and not as a set, in most cases. This is one of the possible solutions - correctly create your set in the application so that it looks like this:

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

- and then use the resulting set inside the JOIN . Also, it will be great if MySQL can accept a static list in ANY subqueries - as for the IN keyword, but it cannot, ANY also expects a set of rows, not a list (which will be considered as a row with columns N , where N is number of items in your list).

Fortunately, in your particular case, your problem has an important limitation: the list cannot contain more elements than the rows in the foo table (otherwise it makes no sense). This way you can dynamically create this 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 repeated using ELT() , so you can pass it directly to the request without additional restructuring. Note that this method, however, will iterate over all foo entries to create a rowset, so it will work, but running the material in the application may be more useful in terms of performance.

A demo for your table can be found here .

Answer 2

perhaps this may 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 - December 06

---

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'); 

request:

 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 | 

Answer 3

Bad way first (without ordered analytic functions, or rank / row_number)

 sel tmp.min_effective_date, for_id.id from ( Sel crossed.effective_date,max(SRC.effective_date) as min_effective_date from foo as src cross join foo as crossed where src.effective_date <cross.effective_date and crossed.effective_date in (given dates here) group by 1 ) tmp inner join foo as for_id on tmp.effective_date =for_id.effective_date 

Next, with row_number

 SEL TGT.id, TGT.effective_date (Sel id, effective_date, row_number() over(order by effective_date asc) as ordered ) SRC INNER JOIN (Sel id, effective_date, row_number() over(order by effective_date asc) as ordered ) TGT on src.ordered+1=TGT.ordered where src.effective_date in (given dates) 

with ordered analytic functions:

 sel f.id, tmp.eff foo as f inner join (SEL ID, max(effective_date) over(order by effective_date asc ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as eff from foo ) TMP on f.id = tmp.id where f.effective_date in (given dates) and tmp.eff is not null 

Required queries must be selected, and the identifiers in the source do not match the same sequence (for example, ascending) as dates. Otherwise, you can directly use the ordered analytic function.