3

I have a MySQL table which contains statistics about function usage for a program. What I retrieve from it basically looks like this (top 15 total here) :

SELECT function_id, data_timer, SUM( data_counter ) total 
FROM data
GROUP BY function_id 
ORDER BY total DESC

+-------------+------------+-------+
| function_id | data_timer | total |
+-------------+------------+-------+
|          56 |        567 |  4389 |
|          23 |       7880 |  1267 |
|           7 |        145 |   812 |
|         ... |        ... |   ... |
+-------------+------------+-------+

Since those results are used in a website module where the user can select which column will be used to ORDER BY as well as between ASC and DESC, I needed to retrieve the rank of each row of the results.

With the help of this question, I was able to assign a rank to each row of the results :

SET @rank = 0;
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data 
WHERE client_id = 2 
GROUP BY function_id 
ORDER BY total DESC

+------+-------------+------------+-------+
| rank | function_id | data_timer | total |
+------+-------------+------------+-------+
|    1 |          56 |        567 |  4389 |
|    2 |          23 |       7880 |  1267 |
|    3 |           7 |        145 |   812 |
|  ... |         ... |        ... |   ... |
+------+-------------+------------+-------+

I am now having some difficulties trying to invert this table, meaning I would like to have the results sorted with the least used function first. Something like this (supposing there are 76 functions) :

+------+-------------+------------+-------+
| rank | function_id | data_timer | total |
+------+-------------+------------+-------+
|   76 |          44 |        346 |     1 |
|   75 |           2 |       3980 |     4 |
|   74 |          13 |        612 |     7 |
|  ... |         ... |        ... |   ... |
+------+-------------+------------+-------+

Here is my SQL query attempt :

SELECT rank, function_id, data_timer, total
FROM
(
SET @rank = 0;
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data 
WHERE client_id = 2 
GROUP BY function_id 
ORDER BY total DESC
)
ORDER BY rank DESC

It keeps popping me this :

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'SET @rank = 0' at line 4

Since I'm not too skilled with SQL, I guess I'm missing something obvious.

Any help will be gladly appreciated, thanks!

Community
  • 1
  • 1
Alexis Leclerc
  • 1,303
  • 1
  • 16
  • 26
  • If you're ordering by total DESC, inverting would be ordering by total ASC, no? Why use the sub-query? – Hart CO Jun 04 '13 at 16:20
  • "*I would like to have the results sorted with the least used function first.*" What represents this value? `SUM(data_counter)', something else? – PM 77-1 Jun 04 '13 at 16:25

2 Answers2

2

You are trying to assign a variable inside of your sub-query. This won't work. Move the assignment outside of your sub-query and it should run.

SET @rank = 0;
SELECT rank, function_id, data_timer, total
FROM
(
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data 
WHERE client_id = 2 
GROUP BY function_id 
ORDER BY total DESC
)
ORDER BY rank DESC
Neil Knight
  • 47,437
  • 25
  • 129
  • 188
1

Another option is to initialize your @rank variable in your query instead of a separate statement:

SELECT rank, function_id, data_timer, total
FROM
(
    SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
    FROM data, 
        (SELECT @rank := 0 ) r
    WHERE client_id = 2 
    GROUP BY function_id 
    ORDER BY total DESC
) r
ORDER BY rank DESC
sgeddes
  • 62,311
  • 6
  • 61
  • 83