user_id category suburb dated walk_time 1 experience 2016-04-09 5 1 discovery 2016-04-09 5 1 experience uk 2016-04-09 5 1 experience aus 2016-04-23 10 2 actions ind 2016-04-15 2 2 actions ind 2016-04-15 1 2 discovery 2016-04-21 2 3 discovery fr 2016-04-12 3 3 emotions ind 2016-04-23 3 3 discovery uk 2016-04-12 4 3 experience ind 2016-04-12 3 i trying every users used category,suburb,dated,walk_time
so resulting table be
user_id category suburb dated walk_time 1 experience 2016-04-09 5 2 actions ind 2016-04-15 2 3 discovery ind 2016-04-12 3 the query trying here is
select user_id, substring_index(group_concat(suburb order cnt desc), ',', 1) suburb_visited, substring_index(group_concat(category order cct desc), ',', 1) category_used, substring_index(group_concat(walk_time order wct desc), ',', 1) walked, substring_index(group_concat(dated order nct desc), ',', 1) dated_at (select user_id, suburb, count(*) cnt,category, count(*) cct, walk_time, count(*) wct, dated,count(*) nct temp_user_notes group user_id, suburb,category,walk_time,dated ) upv group user_id;
select user_id, (select category temp_user_notes t1 t1.user_id = t.user_id group category order count(*) desc limit 1) category, (select suburb temp_user_notes t2 t2.user_id = t.user_id group suburb order count(*) desc limit 1) suburb, (select dated temp_user_notes t3 t3.user_id = t.user_id group dated order count(*) desc limit 1) dated, (select walk_time temp_user_notes t4 t4.user_id = t.user_id group walk_time order count(*) desc limit 1) walk_time (select distinct user_id temp_user_notes) t 