Lannister Lannister - 6 months ago 11
SQL Question

how to get count of values in mysql

I have a table user_places like this:

user_id recorded_date place_visited
2 2016-04-05 NY
2 2016-04-07 UK
2 2016-04-08 UK
2 2016-04-08 UK
3 2016-04-08 AUS
3 2016-04-09 AUS
2 2016-04-15 NY


I am trying to get the latest recorded_date of user and name of the place that he has visited the most.

Answer

If your data is not too large, then you can use this trick in MySQL:

select user_id, max(dte),
       substring_index(group_concat(place_visited order by cnt desc), ',', 1)
from (select user_id, place_visited, count(*) as cnt, max(recorded_date) as dte
      from user_places
      group by user_id, place_visited
     ) upv
group by user_id;