CODEWITHSUNDEEP
mysqlsql
Toskan
Toskan

Reputation: 14931

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

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:

enter image description here

or in prosa

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

Upvotes: 1

Views: 2520

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

Sanjiv Dhakal
Sanjiv Dhakal

Reputation: 316

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

Upvotes: 0

Related Questions