CODEWITHSUNDEEP
mysqlsql
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

MySQL MAX() with GROUP BY

Considering these entries:

INSERT INTO `schedule_hours` (`id`, `weekday`, `start_hour`) VALUES
(1, 1, '09:00:00'),
(2, 2, '09:00:00'),
(3, 3, '09:00:00'),
(4, 4, '09:00:00'),
(5, 5, '09:00:00'),
(6, 6, NULL),
(7, 7, NULL),
(8, 1, '12:00:00');

I'm running the following query:

SELECT MAX(id), weekday, start_hour
FROM schedule_hours
GROUP BY weekday
ORDER BY weekday

The objective is to get a whole week (weekday 1-monday, 2-tuesday, etc...) but return the most recent entries.

So, in my table I now have 2 entries for Monday and 1 entry for the rest of the days, I only want to return the latest ones (id is an increment field), the right result should be:

8 1 12:00:00
2 2 09:00:00
3 3 09:00:00
4 4 09:00:00
5 5 09:00:00
6 6 NULL
7 7 NULL

What I'm currently getting:

8 1 09:00:00 < wrong
2 2 09:00:00
3 3 09:00:00
4 4 09:00:00
5 5 09:00:00
6 6 NULL
7 7 NULL

The id and weekday columns are correct, but the first row is showing a wrong result for the start_hour column!

Upvotes: 0

Views: 187

Answers (2)

Kickstart
Kickstart

Reputation: 21513

An alternative which avoids taking advantage of MySQL allowing a GROUP BY of a field which isn't in the SELECT statement:-

SELECT schedule_hours.id, schedule_hours.weekday, schedule_hours.start_hour
FROM schedule_hours
INNER JOIN
(
    SELECT weekday, MAX(id) AS MaxId
    FROM schedule_hours
    GROUP BY weekday
)Sub1
ON schedule_hours.id = Sub1.MaxId
AND schedule_hours.weekday = Sub1.weekday
ORDER BY schedule_hours.weekday

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You should try this query:

SELECT id, weekday, start_hour
FROM schedule_hours
WHERE id IN (
     SELECT MAX(id)
     FROM schedule_hours
     GROUP BY weekday
   )
ORDER BY weekday

Currently in your query, the columns in SELECT clause are different from the columns in GROUP BY clause. In standard SQL, your query is illegal and will result in a syntax error. However, MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause, which is why you are not getting an error but the output is not what you are expecting. For more details, you may read MySQL Extensions to GROUP BY.

Upvotes: 3

Related Questions