SQL Group By Order BY Descending on datetime column

Question

I have a services table with the following column-rows on a MySQL database

`id`    `service_rendered`  `created_at`
 1       repair              2016-11-19 14:40:56
 2       install             2016-11-19 14:40:58
 3       repair              2016-11-19 14:44:27
 4       install             2016-11-19 14:50:35

I'm trying to count the number of services and the last date/time it was rendered. Column created_at is a datetime type.

The SQL statement that I'm running is:

SELECT COUNT(`service_rendered`) as `count_service`, `service_rendered`, `created_at` as `last_rendered`
FROM services
WHERE `id` IN (1,2,3)
GROUP BY `service_rendered`
ORDER BY `created_at` DESC

But what I'm getting is:

`count_service`    `service_rendered`  `last_rendered`
 2                  repair              2016-11-19 14:40:56
 1                  install             2016-11-19 14:40:58

How do I write my SQL so that I am able to get 2016-11-19 14:44:27 on repair?

Answer 1

You can use the following solution using MAX on the created_at column:

SELECT COUNT(`service_rendered`) AS `count_service`, `service_rendered`, MAX(`created_at`) AS `last_rendered`
FROM services
WHERE `id` IN (1,2,3)
GROUP BY `service_rendered`
ORDER BY `last_rendered` DESC

demo: http://sqlfiddle.com/#!9/3906b7/2

You want to ORDER BY created_at DESC?

You have to use the alias of the column last_rendered, so replace ORDER BY created DESC with ORDER BY last_rendered DESC