1

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 :)

turrican_34
  • 679
  • 1
  • 7
  • 27
  • you are not ordering by check date. add checkdate also in the order by in 3rd query – jophab May 15 '17 at 14:02
  • I tried that but then it just sorts the results in order by date. What I need is the most recent row from each of the 10 columns. So if there is 50 columns with monkey as the value I needthe most recent monkey row. The 3rd query gets the first monkey row. – turrican_34 May 15 '17 at 14:06
  • You can oder by with multiple columns. see http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering – jophab May 15 '17 at 14:07
  • I don't know if this will work for you but it shows how to add multiple columns to your order by. Also you'll need to add check_date to your group by since your query uses aggregation. SELECT animal, check_date, count(\*) FROM mammals GROUP BY animal, check_date ORDER BY check_date, count(\*) DESC LIMIT 10 – Display name May 15 '17 at 14:08
  • @Dys mondad , thanks but thats not working. The 3rd query returns exactly what I need except it gets the first row of each result when I need the last most recent row. – turrican_34 May 15 '17 at 14:21
  • @turrican_34, you'll have to play a bit with the sort order. You do know that DESC is descending and ASC is ascending? Also remove the LIMIT clause for now and that may help you figure out where the row that you're looking for is returning. Also know that sort order is sort of random on columns in which no sorting is specified. The SQL engine will return those rows in the order in which it discovered the data. – Display name May 15 '17 at 14:26
  • @Dys mondad, Ive played around with the sort order, but it hasn't helped. I've just updated the OP to make the issue a little clearer. – turrican_34 May 15 '17 at 16:33

1 Answers1

1

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
Display name
  • 1,228
  • 1
  • 18
  • 29