Menu
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!
842
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
we can use rank function and group by in the same query set but all the columns should be contained in either aggregate function or the Group by clause.
SELECT id_student, id_class, grade, @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn, @class:=id_class AS clset FROM (SELECT @student:= -1) s, (SELECT @class:= -1) c, (SELECT * FROM mytable ORDER BY id_class, id_student ) t 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 the id_class (which is stored in @class) is not equal to the current value (which is stored in id_class), the @student is zeroed. Otherwise is is incremented.@class is assigned with the new value of id_class, and it will be used in test on step 3 at the next row.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
