Jaime Dolor jr. Jaime Dolor jr. - 2 years ago 109
SQL Question

SQL Group By Order BY Descending on datetime column

I have a

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
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 Source

You can use the following solution using MAX on created_at:

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download