What is the other way to get result without using MYSQL ROW_NUMBER Function with PARTITION and multiple group by to reset row count

Question

select 
   Id,request_id,key_skill_id, 
   ROW_NUMBER() OVER (PARTITION BY skill_id 
                      ORDER BY request_id,skill_id) sequence 
from report  
where id= x 
GROUP by request_id, skill_id 
order by  request_id,skill_id ;

I tried to write something like the following, but the result is not the same:

select id, request_id, @skill_id :=skill_id as skill_id, @row_number := CASE WHEN @skill_id = skill_id THEN @row_number+1 ELSE 1 END AS row_number from report, (SELECT @row_number := 0, @skill_id := '') as t where id =x
GROUP by request_id, skill_id order by request_id, skill_id;

Answer 1

The original window function strikes me as a bit odd but I confess that I don't use these functions too frequently being confined to MySQL 5.7 myself. The PARTITION BY clause specifies the key_skill_id column so re-numbering 1, 2, 3, etc. will be done on those rows with identical key_skill_id column values. But then there is a final ORDER clause at the very end of the SQL that re-sorts the results so that rows with the same key_skill_id will not in general be together (unless, for example, there was only a single value of feedback_request_id being selected).

To do the initial numbering the rows, however, the table must first be sorted by key_skill_id and then feedback_request_id. The purpose of the GROUP BY clause in the original SQL is to function as an equivalent of a SELECT DISTINCT query, which can't be used because the added row number column guarantees that each row is distinct. The reason why the GROUP BY works is that it is applied before the ROW_NUMBER window function is performed whereas the SELECT DISTINCT implied filtering would be applied after the ROW_NUMBER function is performed.

Given you have provided no table definitions, data, expected output, etc. I was unable to test the following. This is my best guess:

select
    x.*,
    @row_number :=
      CASE
         WHEN  @key_skill_id = x.key_skill_id THEN @row_number+1
         ELSE 1
      END AS sequence,
    @key_skill_id = x.key_skill_id
from (
    select distinct /* to emulate group by */
       candidateId,
       feedback_id,
       key_skill_id
       from newFeedbackReport
       where candidate_id = 2501
       order by key_skill_id, feedback_request /* this is not a mistake */
) x,
(SELECT @row_number := 0, @key_skill_id := '') as t
order by feedback_request_id, key_skill_id;