Search code examples
mysqlsqlselectcountwindow-functions

Is it possible to run the rank function on top of a query in MYSQL

I am trying to learn windows function in MY SQL. All I want to know is that if I can do a rank function using a query in the from clause like I have shown below in MYSQL.

SELECT s.classroom
     , rank() over ( order by s.cnt desc) row_no
  from (
select classroom,count(*) as cnt
  from list
 group 
    by classroom
)s;
Solution

  • Yes, your query is valid SQL - assuming that you are running MySQL 8.0, since earlier versions do not support window functions such as rank().

    Note, however, that you don't actually need a subquery for this. You can use the window function directly in the aggregate query:

    select
    classroom,
    count(*) cnt,
    rank() over(order by count(*) desc) row_no
from list
group by classroom