Max() + group by not working as expected in mysql

Question

CREATE TABLE IF NOT EXISTS `order_order_status` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_status_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_order_status_order_status_id_index` (`order_status_id`),
  KEY `order_order_status_order_id_index` (`order_id`),
  KEY `order_order_status_created_at_index` (`created_at`),
  KEY `order_order_status_updated_at_index` (`updated_at`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `order_order_status`
--

INSERT INTO `order_order_status` (`id`, `order_status_id`, `order_id`, `created_at`, `updated_at`) VALUES
(1, 2, 1, '2016-10-01 01:57:37', '2016-10-01 01:57:37'),
(2, 2, 2, '2016-10-01 01:57:54', '2016-10-01 01:57:54'),
(3, 2, 3, '2016-10-02 02:12:49', '2016-10-02 02:12:49'),
(4, 6, 3, '2016-10-02 02:14:19', '2016-10-02 02:14:19');

What i want to select is:

1, 2, 1, '2016-10-01 01:57:37', '2016-10-01 01:57:37'
2, 2, 2, '2016-10-01 01:57:54', '2016-10-01 01:57:54'
4, 6, 3, '2016-10-02 02:14:19', '2016-10-02 02:14:19'

that is the newest entry of order_order_status grouped by order_id

now the problem:

running

select *, max(created_at) from `order_order_status` group by `order_order_status`.`order_id`

returns me:

or in prosa

it returns me NOT the newest entry, instead it returns the older one for order_id 3

MySQL doesn't guarantee which row will be returned in this case. It just picks one "randomly". In that sense, it's working as expected. ["the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate"](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) — sstan, Oct 02 '16 at 03:48

Gordon Linoff · Answer 1 · 2016-10-02T11:56:39.267

2

MySQL is working exactly as expected. The problem is your expectations.

select * with a group by doesn't make sense. You want to get the maximum, do something like this:

select oos.*
from order_order_status
where oos.created_at = (select max(oos2.created_at)
                        from order_order_status oos2
                        where oos2.order_id = oos.order_id
                       );

Aggregation (group by) produces one row per group. An aggregation function such as max() gets the maximum value of a column -- nothing more. It just operates on a column.

When you use select *, you have a bunch of columns that are not in the group by and not the arguments to aggregation columns. MySQL allows this syntax (unfortunately -- few other databases do). The values for the unaggregated columns are arbitrary values from indeterminate rows in the group.

edited Oct 02 '16 at 11:56

answered Oct 02 '16 at 03:49

Gordon Linoff

1,242,037
58
646
786

` from group by` ? oos and oos2 arent defined – Toskan Oct 02 '16 at 03:53
why not just use something simpler like this: http://stackoverflow.com/questions/18221999/group-by-having-max-date/35985225#35985225 – Toskan Oct 02 '16 at 05:09
@Toskan . . . There was a typo in my query. And, this is going to work best with an index on `order_order_status(order_id, created_at)`. Without that index, putting the subquery in the `where` clause would probably have better performance. – Gordon Linoff Oct 02 '16 at 11:56

score 0 · Answer 2 · answered Oct 02 '16 at 04:18

0

using order by order_id desc may solve your problem

select *, max(created_at) from `order_order_status` group by `order_order_status`.`order_id` order by `order_order_status`.`order_id` desc

answered Oct 02 '16 at 04:18

Sanjiv Dhakal

316
3
13

Max() + group by not working as expected in mysql

2 Answers2