I am not getting the row_num() properly in the DESC order

user_15124396 0
Viewed 65

I am not getting the row_num() properly in the DESC order

What I did:

  • Find the total email sent by each user using the COUNT() function.
  • Group records by from_user.
  • Use the formula ROW_NUMBER to order records by total emails in descending order.
  • Order users with the same number of emails alphabetically

My query:

SELECT from_user,
       COUNT(from_user) as email_sent,
       row_number() OVER (ORDER BY count(from_user) DESC) as rnk
FROM google_gmail_emails
GROUP BY 1
ORDER BY COUNT(from_user) DESC,
         from_user asc
mysql sql
1 Answers
  select 
    from_user, 
    count(from_user) as email_sent,
    row_number() over (order by from_user asc) as alpha
    from google_gmail_emails 
    group by 1 order by count(from_user) desc, alpha;

this query will ordered records by total emails in descending and give the alphabetically sorted order row number in alpha column.

user_43258990
Related