First I need to get to get the 10 most common column values, and then from them values I need the most recent row.
I can get the 10 most recent column values easily enough like this:
SELECT animal, count(*) FROM mammals GROUP BY animal ORDER BY count(*) DESC LIMIT 10;
And I can also get the most recent row of a value like this:
SELECT * FROM mammals where animal = 'monkey' ORDER BY check_date DESC LIMIT 1;
I need to do both of these in one query if possible.
The closest I've got does everything I need except it doesn't get the most recent row of the value, it gets the first row.
SELECT animal, check_date, count(*) FROM mammals GROUP BY animal ORDER BY count(*) DESC LIMIT 10;
EDIT:
I want to add a bit more info because I'm not sure it's clear enough.
If I have a table like this:
+------------------------+---------------------+
| Monkey | 2017-05-08 19:35:30 |
| Monkey | 2017-05-09 08:33:16 |
| Giraffe | 2017-05-09 08:35:24 |
| Giraffe | 2017-05-09 09:09:25 |
| Monkey | 2017-05-09 09:22:43 |
| Giraffe | 2017-05-09 09:24:23 |
| Giraffe | 2017-05-09 09:25:07 |
| Monkey | 2017-05-09 09:26:00 |
| Lion | 2017-05-09 09:26:17 |
| Lion | 2017-05-09 09:28:35 |
| Gazelle | 2017-05-09 09:29:34 |
| Monkey | 2017-05-09 13:29:39 |
| Gazelle | 2017-05-09 13:35:01 |
| Gazelle | 2017-05-09 14:52:57 |
+------------------------+---------------------+
And I run the 3rd query above I end up with this:
+------------------+---------------+----------------+
| Animal | check_date | count(*) |
+------------------+---------------+----------------+
| Monkey | 2017-05-08 19:35:30 | 5 |
| Giraffe | 2017-05-09 08:35:24 | 4 |
| Gazzelle | 2017-05-09 09:29:34 | 3 |
| Lion | 2017-05-09 09:26:17 | 2 |
+------------------+---------------+----------------+
The table would obviously be much larger than this, but imagine those are the most common values. As you can see the row the 3rd query returns is the oldest row, but I need the most recent row. So for Monkey it would be '2017-05-09 13:29:39'.
I need the table to look like this:
+------------------+---------------+----------------+
| Animal | check_date | count(*) |
+------------------+---------------+----------------+
| Monkey | 2017-05-09 13:29:39 | 5 |
| Giraffe | 2017-05-09 09:25:07 | 4 |
| Gazzelle | 2017-05-09 14:52:57 | 3 |
| Lion | 2017-05-09 09:28:35 | 2 |
+------------------+---------------+----------------+
Thanks :)
解決方案
This should do what you want.
select animal, max(check_date) as max_date, count(*) as count
from mammals
group by animal
order by count(*) desc
LIMIT 10;
This is my output from the above query based on your sample data.
Monkey | 2017-05-09 13:29:39.000 | 5
Giraffe | 2017-05-09 09:25:07.000 | 4
Gazelle | 2017-05-09 14:52:57.000 | 3
Lion | 2017-05-09 09:28:35.000 | 2