How to rank for this query-CodePudding

I want to generate the below result and here is my query

SET @count:=0;
SELECT  Workout.created_by, SUM(Workout.training_load) as TL, FB_User.name
from FB_Workout
INNER join FB_User ON FB_Workout.created_by = FB_User.id
where FB_Workout.created_at between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
group by created_by
order by TL desc

created_by	TL	name	RANK
1234	56789	ROCK	1
2345	23478	Undertaker	2
8907	12900	KANE	3

Where do i need to use RANK() to get out put like above, with the query that I posted I'll get above table without Rank , but i need Rank assigned.

CodePudding user response：

WITH workout_summary AS
  (
   SELECT 
     w.created_by, 
     SUM(w.training_load) AS TL, 
     u.name 
   FROM
     FB_Workout AS w 
   INNER JOIN FB_User AS u ON w.created_by = u.id 
   WHERE w.created_at BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() 
   GROUP BY created_by 
   )

SELECT 
  *,
  RANK() OVER (ORDER BY TL DESC)
FROM workout_summary

CodePudding user response：

WITH workout_summary AS
 (
  SELECT 
    w.created_by, 
    SUM(w.training_load) AS TL, 
    u.name 
  FROM
    FB_Workout AS w 
  INNER JOIN FB_User AS u ON w.created_by = u.id 
  WHERE w.created_at BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() 
  GROUP BY created_by 
  )

SELECT 
  *,
  RANK() OVER (ORDER BY TL DESC)
FROM workout_summary