0

I want to have the top cathegory value for each state

SELECT DISTINCT COUNT(tweet_category) AS category_count, tweet_category, tweet_state
FROM [New_years_resolutions_2020]
GROUP BY tweet_state, tweet_category
ORDER BY tweet_state, category_count DESC;
catehgory_count tweet_cathegory tweet_state
5 Personal Growth AK
4 Family/Friends AK
3 Health/Fitness AK
2 Humor AK
15 Health/Fitness AL
8 Family/Friends AL
6 Personal Growth AL
9 Humor AL

I would like the result to look like:

catehgory_count tweet_cathegory tweet_state
5 Personal Growth AK
15 Health/Fitness AL
Andrea Wupuy
  • 17
  • 3
  • something like `select max(catehgory_count partition by tweet_state)`.... <<< Look for that – T.S. Mar 02 '23 at 04:22
  • 1
    Wrap your query up as a subquery and take a look at https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group. – T N Mar 02 '23 at 05:15

0 Answers0