So I have a table as follows:
ID_STUDENT | ID_CLASS | GRADE
-----------------------------
1 | 1 | 90
1 | 2 | 80
2 | 1 | 99
3 | 1 | 80
4 | 1 | 70
5 | 2 | 78
6 | 2 | 90
6 | 3 | 50
7 | 3 | 90
I need to then group, sort and order them to give:
ID_STUDENT | ID_CLASS | GRADE | RANK
------------------------------------
2 | 1 | 99 | 1
1 | 1 | 90 | 2
3 | 1 | 80 | 3
4 | 1 | 70 | 4
6 | 2 | 90 | 1
1 | 2 | 80 | 2
5 | 2 | 78 | 3
7 | 3 | 90 | 1
6 | 3 | 50 | 2
Now I know that you can use a temp variable to rank, like here, but how do I do it for a grouped set? Thanks for any insight!
Modified from above, this works but its more complex than I think it needs to be:
There is a problem with Quassnoi's solution (marked as best answer).
I have the same problematic (i.e. simulating SQL Window Function in MySQL) and I used to implement Quassnoi's solution, using user-defined variables to store previous row value...
But, maybe after a MySQL upgrade or whatever, my query did not work anymore. This is because the order of evaluation of the fields in SELECT is not guaranteed. @class assignment could be evaluated before @student assignment, even if it is placed after in the SELECT.
This is mentionned in MySQL documentation as follows :
source : http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
Finally I have used a trick like that to be sure to assign @class AFTER reading it :
Using left() function is just used to set @class variable. Then, concatenate the result of left() (equal to NULL) to the expected result is transparent.
Not very elegant but it works!
Result:
This works in a very plain way:
id_class
first,id_student
second.@student
and@class
are initialized to-1
@class
is used to test if the next set is entered. If the previous value of theid_class
(which is stored in@class
) is not equal to the current value (which is stored inid_class
), the@student
is zeroed. Otherwise is is incremented.@class
is assigned with the new value ofid_class
, and it will be used in test on step 3 at the next row.I did some searching, found this article to come up with this solution:
Any thoughts on which is better?