How to rank for this query

Question

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.

Please correct the query and/or sample output as the query will not work and it is not consistent with the sample output(rank excluded). — Serg, Apr 29 '22 at 12:08

score 0 · Answer 1 · answered Apr 29 '22 at 11:56

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

score 0 · Answer 2 · answered Apr 29 '22 at 14:59

0

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

answered Apr 29 '22 at 14:59

Harsh Soni

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 30 '22 at 15:13

How to rank for this query

2 Answers2