MYSQL: closest to delivery date grouped by user_id

Question

I need to get some rows with the closest date date_added, but not beyond the date provided by the user, grouped by user_id.

I looked at a bunch of max in group type answers, but I'm not quite there:

Get the closest entries for a specific date, grouped by type

SQL Query to show the nearest date?

Find near in given datetime in mysql query

Get closest date from MySQL table

https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

It's close: qaru.site/questions/86492 / ... . Finds the maximum date, although I need the maximum size specified by the user, not the maximum.

A subset of data has already been filtered out with the correct organization_id, framework_id, and level_id parameters.

+----+---------+-----------------+--------------+----------+---------------------+
| id | user_id | organisation_id | framework_id | level_id |     date_added      |
+----+---------+-----------------+--------------+----------+---------------------+
|  2 |       1 |               2 |            1 |        1 | 2015-07-31 14:02:49 |
|  9 |       2 |               2 |            1 |        1 | 2015-09-01 11:05:09 |
| 11 |       1 |               2 |            1 |        1 | 2015-09-07 14:13:39 |
+----+---------+-----------------+--------------+----------+---------------------+

If the delivery date is 2015-09-07. I expect to see id: 9 and 11.

If the delivery date is 2015-09-01. I expect to see id: 2 and 9.

If the delivered date is 2015-07-31. I expect to see id: 2.

This request is as close as possible:

SELECT  t1.id
    , t1.user_id
    , t1.date_added 
FROM 
    completed_frameworks AS t1
WHERE date_added = (
    SELECT 
        MAX(date_added)
    FROM 
        completed_frameworks
    WHERE 
        user_id = t1.user_id
        AND 
        date_added <= '2015-09-07 23:59:59'
)
AND
(
    t1.organisation_id = 2 
    AND 
    t1.framework_id = 1 
    AND 
    t1.level_id = 1
)

It returns what I expect on a date: 2015-09-07

When the date is 2015-09-01, it returns only the identifier 9. Not also 2, as I expected.

When the date is 2015-07-31, it returns 0 rows.

Let me know if there is anything else I can provide.

Hurrah!

EDIT:

Thanks for the answers so far. I need to clarify two points:

1) . - . n . , date_added , .

2) . datepicker. 23:59:59, .

Answer 1

. organisation_id/framework_id/level_id , .

SELECT t1.id, t1.user_id, t1.date_added 
FROM 
    completed_frameworks AS t1
WHERE date_added = (
    SELECT 
        MAX(date_added)
    FROM 
        completed_frameworks
    WHERE 
        user_id = t1.user_id
    AND 
        date_added <= '2015-09-01 23:59:59'
    AND
        organisation_id = 2 
    AND 
        framework_id = 1 
    AND 
        level_id = 1
)

Answer 2

, '2015-09-07 14:13:39'. . completed_frameworks, , .

SELECT t1.id, t1.user_id, t1.date_added
FROM completed_frameworks t1
INNER JOIN
(
    SELECT cf.date_added
    FROM completed_frameworks cf
    GROUP BY cf.date_added
    HAVING cf.date_added <= '2015-09-07 14:13:39'
    ORDER BY cf.date_added DESC
    LIMIT 2
) t2
ON t1.date_added = t2.date_added
WHERE t1.organisation_id = 2 AND t1.framework_id = 1 AND t1.level_id = 1

Answer 3

, , :

SELECT f.id, f.user_id  f.date_added 
FROM completed_frameworks f
WHERE date(f.date_added) <= '2025-09-07'  -- or whatever your date is
ORDER BY f.date_added DESC
LIMIT 2;

EDIT:

, , :

select f.*
from completed_frameworks f join
     (select user_id, max(date_added) as maxda
      from completed_frameworks
      where date(date_added) <= '2015-09-07'
      group by user_id
     ) u
     on f.user_id = u.user_id and f.date_added = maxda