Lannister Lannister - 1 year ago 75
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 Source

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;