User-defined variable in ranking statement

Question

I will appreaciate any help on this issue. I already spent hours without any real solution. I have a SQL

SELECT to_place, rank
FROM
  (SELECT g1.to_place as to_place, g1.pcount as pcount, 
                @rank := IF(@current_to_place = g1.to_place, @rank + 1, 1) AS rank,
                @current_to_place := g1.to_place 
  FROM 
          (select 
          to_place, count(*) as pcount
          from temp_workflows
          group by to_place
          order by to_place,pcount desc) g1
  ORDER BY g1.to_place, g1.pcount DESC) ranked

In table g1, I am grouping my data to find the most common occurrence of to_place.And then I want to rand those occurrences in ascending order (so I can later select top 3 of the most common occurrences per each to_place category.

The issue is that the user-defined variable is unpredictable (@rank is sometimes always 1) which probably is related to the fact that in one statement, I should not reference the same variable (current_to_place). I read a lot about using separate statements etc. but I could find a way to write my statement in a different way. How can I define @current_to_place elsewhere so the result is the same? Thanks in advance for your help.

Sample data would be nice also be aware that rank becomes a reserved word in version 8 — P.Salmon, Mar 27 '21 at 13:50

score 0 · Answer 1 · answered Mar 27 '21 at 14:12

I think you should be testing pcount to get rank and you should initialise variables

DROP TABLE IF EXISTS T;
CREATE TABLE T
(to_place int);

insert into t values (1),(2),(2),(3),(3),(3);
SELECT to_place, rank
FROM
  (
  SELECT g1.to_place as to_place, g1.pcount as pcount, 
                @rank := IF(@current_to_place <> pcount, @rank + 1, 1) AS rank,
                @current_to_place := pcount 
  FROM 
          (select 
          to_place, count(*) as pcount
          from t
          group by to_place
          order by to_place,pcount desc) g1
 cross join(select @rank:=0,@current_to_place:=0) r
  ORDER BY g1.pcount DESC
  )
   ranked

+----------+------+
| to_place | rank |
+----------+------+
|        3 |    1 |
|        2 |    2 |
|        1 |    3 |
+----------+------+
3 rows in set (0.016 sec)

Great, initializing the variables is what fixed the issue, even though I am not really sure I understand the logic since I thought that the variable is initized at the moment of the first use of the variable. But anyway, thanks for your help. — Ivo Brabec, Mar 28 '21 at 19:45

User-defined variable in ranking statement

1 Answers1