There is a mySQL table with a list of tasks

id | mid | s | e | date 1 | 15 | 1 | 3 | 2017-01-17 00:00:00 2 | 15 | 1 | 4 | 2017-01-24 00:00:00 3 | 20 | 2 | 6 | 2017-01-23 00:00:00 4 | 20 | 2 | 7 | 2017-02-02 00:00:00 5 | 20 | 2 | 8 | 2017-02-07 00:00:00 6 | 34 | 3 | 5 | 2016-12-31 00:00:00

The goal is to select the lines in which the maximum date ends in two weeks. That is, from the variant above, it is necessary to get mid 15 and 34. The selection criterion should not exceed today’s maximum for each unique mid today + 2 weeks; I hope clearly explained.

Tell me where to start, that somehow I wondered how to get such data. Tried requests with group by and with DISTINCT but they all did not do what was needed.

Or is it easier to take all the data and filter it out in PHP?

  • Start with the most common request for getting the maximum date (MAX) for each (GROUP BY) mid. Continue screening records (HAVING) that do not fall within the specified range. Having received mid-date pairs, get full records from the second copy of the table. - Akina
  • То есть из варианта выше необходимо получить mid 15 и 34 Are you sure about mid = 34? - korytoff
  • Yes mid = 34 can also enter as they are not higher today + 2 weeks - jemxx

2 answers 2

Window functions allow you to achieve the desired result, although MySql still does not support them.

But maybe good, because window functions last longer than multiple queries. There are several options:

  • use subquery
  • 2 requests one by one
  • use view or temporary table

I will describe the variant with the subquery:

 SELECT t1.* FROM `tasks` t1 INNER JOIN ( SELECT `mid`, MAX(`date`) AS `date` FROM `tasks` WHERE `DATE` > CURDATE() AND `DATE` < ADDDATE(CURDATE(), 14) GROUP BY `mid` ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`;

Be sure to use indexes for mid and date . In this example, we first select the desired dates by unique mid , and then get all the other data.

UPD

So that mid 34 is also included in the selection in the condition of the WHERE predicate; we remove the expression DATE > CURDATE() AND

UPD2 According to @Akina re-read the question and realized that the result should still be different. The question refers to the maximum grouping date, and not just the date. Correct request:

 SELECT t1.* FROM `tasks` t1 INNER JOIN ( SELECT `mid`, MAX(`date`) AS `max_date` FROM `tasks` GROUP BY `mid` HAVING `max_date` < ADDDATE(CURDATE(), 14) ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`max_date`;
  • Try your query on the data provided. mid = 20 will be sampled, although it should not. - Akina
  • Yes, of course I will try. The logic is clear, then I think I'll figure it out - jemxx
  • @Akina not sure what should not. According to the logic of the author - should. In the question itself, the logic and the example of the result do not agree a bit - korytoff
  • 20 should not be included, as there is a date outside the range today + 2 weeks (2017-02-07). In all other mid dates, the date does not go up, so it suits us. Slightly incorrectly explained initially ... - jemxx
  • @korytoff, correct your request and move the check to the time range in HAVING. Then the request will correspond to the task. - Akina
 mysql> create table tasks -> (id int,mid int,s int,e int,`date` datetime); Query OK, 0 rows affected (0.06 sec) mysql> insert into tasks -> (id,mid,s,e,`date`) -> values -> (1,15,1,3,'2017-01-17 00:00:00'), -> (2,15,1,4,'2017-01-24 00:00:00'), -> (3,20,2,6,'2017-01-23 00:00:00'), -> (4,20,2,7,'2017-02-02 00:00:00'), -> (5,20,2,8,'2017-02-07 00:00:00'), -> (6,34,3,5,'2016-12-31 00:00:00'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT t1.* -> FROM `tasks` t1 -> INNER JOIN ( -> SELECT `mid`, MAX(`date`) AS `date` -> FROM `tasks` -> WHERE `DATE` > CURDATE() AND `DATE` < ADDDATE(CURDATE(), 14) -> GROUP BY `mid` -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`; +------+------+------+------+---------------------+ | id | mid | s | e | date | +------+------+------+------+---------------------+ | 2 | 15 | 1 | 4 | 2017-01-24 00:00:00 | | 3 | 20 | 2 | 6 | 2017-01-23 00:00:00 | +------+------+------+------+---------------------+ 2 rows in set (0.03 sec) mysql> SELECT t1.* -> FROM `tasks` t1 -> INNER JOIN ( -> SELECT `mid`, MAX(`date`) AS `date` -> FROM `tasks` -> WHERE /* `DATE` > CURDATE() AND */ `DATE` < ADDDATE(CURDATE(), 14) -> GROUP BY `mid` -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`; +------+------+------+------+---------------------+ | id | mid | s | e | date | +------+------+------+------+---------------------+ | 2 | 15 | 1 | 4 | 2017-01-24 00:00:00 | | 3 | 20 | 2 | 6 | 2017-01-23 00:00:00 | | 6 | 34 | 3 | 5 | 2016-12-31 00:00:00 | +------+------+------+------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT t1.* -> FROM `tasks` t1 -> INNER JOIN ( -> SELECT `mid`, MAX(`date`) AS `date` -> FROM `tasks` -> GROUP BY `mid` -> HAVING /* `DATE` > CURDATE() AND */ MAX(`DATE`) < ADDDATE(CURDATE(), 14) -> ) t2 ON t1.`mid` = t2.`mid` AND t1.`date` = t2.`date`; +------+------+------+------+---------------------+ | id | mid | s | e | date | +------+------+------+------+---------------------+ | 2 | 15 | 1 | 4 | 2017-01-24 00:00:00 | | 6 | 34 | 3 | 5 | 2016-12-31 00:00:00 | +------+------+------+------+---------------------+ 2 rows in set (0.00 sec) mysql>