-------------------------
| ID | RID | DATE | // TABLE A
-------------------------
| 1 | 1 | 2015-01-01 | // 1st edition
| 2 | 2 | 2015-01-01 | // 1st edition
| 3 | 1 | 2015-02-01 | // 2nd edition
| 4 | 4 | 2015-01-01 | // 1st edition
| 5 | 1 | 2015-05-01 | // 3rd edition
| 6 | 6 | 2015-01-01 | // 1st edition
| 7 | 6 | 2015-01-10 | // 3rd edition
| 8 | 6 | 2015-01-12 | // 4th edition
| 9 | 6 | 2015-01-02 | // 2nd edition
-------------------------
Table A consists of entries describing “file editions”. So far, I was able to create a SELECT statement that would select the latest edition with this:
SELECT `t1`.`id` AS` id`,` t1`.`rid` AS `rid`,` t1`.`date` AS `date`
FROM `table_a`` t1`
WHERE (`t1`.`date` = (
SELECT MAX (`t2`.`date`)
FROM `table_a`` t2`
WHERE (`t2`.`rid` =` t1`.`rid`)
)))
Which gives me something like this:
-------------------------
| ID | RID | DATE | // TABLE B
-------------------------
| 2 | 2 | 2015-01-01 | // 1st edition
| 4 | 4 | 2015-01-01 | // 1st edition
| 5 | 1 | 2015-05-01 | // 3rd edition
| 8 | 6 | 2015-01-12 | // 4th edition
-------------------------
But my question is: how do I tune the SELECT statement so that I get all the latest releases on a specific date? For example, let's say I wanted to find the latest version from 2015-01-11? I would like to get a result similar to table C:
-------------------------
| ID | RID | DATE | // TABLE C
-------------------------
| 1 | 1 | 2015-01-01 | // 1st edition
| 2 | 2 | 2015-01-01 | // 1st edition
| 4 | 4 | 2015-01-01 | // 1st edition
| 7 | 6 | 2015-01-10 | // 3rd edition
-------------------------
, , /: "mysql " ...
, , , , , SELECT MAX WHERE.
: , ; . <= 2015-01-11. - MAX (), ID = RID (.. ). SELECT, .