Inner Join Query with MAX Date Still Returning Multiple Values for Date

Question

I have a query that is to pull at max one entry per day per specified id. The query is returning multiple values per day despite max being specified. I need some assistance tweaking this so that I only get one entry per day. The image shows a snippet of the data that is returned.

    SELECT a.* 
    FROM turtle_derby AS a 
    INNER JOIN (SELECT turtle_id, DATE(`date`) AS day_date, MAX(`date`) AS maxdate 
    FROM turtle_derby 
    GROUP BY turtle_id, day_date) AS groupedtt 
    ON a.turtle_id = groupedtt.turtle_id 
    AND a.turtle_id = '175846' 
    AND a.`date` = groupedtt.maxdate 
    AND a.`date` > '2018-07-26' 
    ORDER BY `a`.`date`

This query assumes that there are no duplicate times in the same day. — Barmar, Mar 19 '19 at 20:20
If there are multiple rows that are all at the last time of the day, which one should be returned? — Barmar, Mar 19 '19 at 20:21

score 0 · Answer 1 · answered Mar 19 '19 at 20:34

Your data has multiple rows are at the same time of the day. If that time is the last time of the day, you'll get all of them. You need to get the maximum of two columns, date and loc_id. See SQL : Using GROUP BY and MAX on multiple columns

SELECT a.* 
FROM turtle_derby AS a 
INNER JOIN (
    SELECT t2.turtle_id, MAX(t2.loc_id) AS max_id
    FROM (
        SELECT turtle_id, MAX(date) AS maxdate
        FROM turtle_derby AS
        GROUP BY turtle_id, DATE(date)
    ) AS t1
    INNER JOIN turtle_derby AS t2
    ON t1.turtle_id = t2.turtle_id AND t1.maxdate = t2.date
    GROUP BY t1.turtle_id, t1.maxdate
) AS groupedtt 
ON a.turtle_id = groupedtt.turtle_id 
AND a.loc_id = groupedtt.maxloc
WHERE a.turtle_id = '175846' 
AND a.`date` > '2018-07-26' 
ORDER BY `a`.`date`

Inner Join Query with MAX Date Still Returning Multiple Values for Date

1 Answers1