MYSQL: closest to supplied date grouped by user_id

Question

I need to get multiple rows with a date_added closest to but not past a user supplied date, grouped by user_id.

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

Get nearest records to specific date grouped by type

Find closest datetime to specified datetime in mysql query

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

This is close: https://stackoverflow.com/a/17038667/5319244. Finds the max date though, I need the max specified by user input, not max outright.

Here's a subset of the data with the correct organisation_id, framework_id and level_id already filtered.

+----+---------+-----------------+--------------+----------+---------------------+
| 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 supplied date is 2015-09-07. I'd expect to see id's: 9 and 11.

If the supplied date is 2015-09-01. I'd expect to see id's: 2 and 9.

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

This query is as close as I got:

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 for the date: 2015-09-07

When the date is 2015-09-01 however it only returns id 9. Not also 2 as I'd expect.

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

Let me know if I there's anything else I can provide.

Cheers!

EDIT:

Thanks for the replies thus far. I need to clarify two points:

1) I don't have a limit. I'm expecting those rows due to the user id's. There could be n users returned. I just want a row for each user where the date_added is closest to the user supplied date.

2) The date supplied will not have a time value. It will be from a simple datepicker UI. In my example query I've added the time of 23:59:59 to encompass all of that day.

score 3 · Accepted Answer · answered Sep 10 '15 at 03:06

3

Try this subquery instead. The organisation_id/framework_id/level_id filter is moved into the subquery and it now returns the right values for the examples you've given.

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
)

answered Sep 10 '15 at 03:06

David Ritchie

46
2

I assumed you actually had more data than you provided in the subset (the giveaway is the word "subset" I suppose), in which case the subquery could potentially return data which would then be filtered out by the "WHERE" statements at the end (e.g. if the most recently added record was for a framework or level other that those queried on). – David Ritchie Sep 10 '15 at 03:39

Tim Biegeleisen · Answer 2 · 2015-09-10T01:55:28.050

Here is a sample query you could use for an input date of '2015-09-07 14:13:39'. The inner query returns the input date along with the next highest date. This temporary table is then used to filter completed_frameworks to give you only records from the two dates most recent to the input date.

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

Gordon Linoff · Answer 3 · 2015-09-10T03:06:32.970

0

I think this does what you want:

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:

If you want one date per user id that is closest to the specified date, I would suggest:

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

edited Sep 10 '15 at 03:06

answered Sep 10 '15 at 02:03

Gordon Linoff

1,242,037
58
646
786

You know more than I do, but I was afraid that his actual data set might have multiple records per date. – Tim Biegeleisen Sep 10 '15 at 02:12
@TimBiegeleisen . . . I'm not sure what that has to do with it. This retrieves the most 2 most recent entries. – Gordon Linoff Sep 10 '15 at 02:41
Thanks guys. I've edited the question for greater clarity. There is no limits. By expected results are due to the user_ids. I'm not looking for the two most recent. – LGH Sep 10 '15 at 02:59
@LGH . . . Oh, that problem is quite different. – Gordon Linoff Sep 10 '15 at 03:06

MYSQL: closest to supplied date grouped by user_id

3 Answers3