RSS LinkedIn Twitter

MySQL: Ranking applied over Multiple Columns

March 3rd, 2011 Categories: Databases, MySQL

I was recently given this problem to work out for a MySQL database. Given a table with the following structure and sample data:

TABLE_1
------------------------------------------------
ID_PK  |  num_1   |  num_2  |  num_3  |
------------------------------------------------
34		200		300	    700
23		400		100	    500
56		100		600	    200
39		900		700	    300

Compute and insert the ranking of that data into a separate table, ranking being defined to roughly mean the row with the highest value for num_1 would be assigned rank 1, and so on. with the expected output as follows:

TABLE_2
-------------------------------------------------------------------------
ID_PK     |  FK_ID  |  Rank_Number_1  |  Rank_Number_2 | Rank_Number_3  |
-------------------------------------------------------------------------
122		34		3		3		1
123		23		2		4		2
124		56		4		2		4
125		39		1		1		3

This can be accomplished using nested select statements, where the rank is computed and added to the results as you pop out of the nesting.

SET @r1=0;
SET @r2=0;
SET @r3=0;
SELECT ID_PK, ranking1, ranking2, ranking3
FROM
(
  SELECT *, @r3:=@r3+1 ranking3
    FROM
    (
	  SELECT *,@r2:=@r2+1 ranking2
	  FROM
	  (
	  SELECT ID_PK, @r1:=@r1+1 ranking1, number_1, number_2, number_3
	  FROM TABLE_1
	  ORDER BY number_1 DESC
	  ) q1
	  ORDER BY number_2 DESC
    ) q2
    ORDER BY number_3 DESC
) q3
ORDER BY ID_PK;

If you wanted to rank more columns, you would need further nesting but following the same pattern allows you to compute as many columns as you want. Back to the original problem, to get these results into TABLE_2, I just preceded the previous SELECT statement with:

INSERT INTO TABLE_2 (FK_ID, Rank_Number_1, Rank_Number_2, Rank_Number_3)
If you’re going to be applying this over a variable number of columns it might make more sense to use a temporary table. In this way you will be able to iterate over each column you want to apply the ranking too, and gradually fill in your TABLE_2.

First create the temporary table:

CREATE TEMPORARY TABLE TABLE_A LIKE TABLE_2;

Then for each column you are going to rank across, run the following query. You would substitute Rank_Number_N, and num_N as needed.

SET @rank=0;
INSERT TABLE_A (Rank_Number_1, FK_ID)
(SELECT @rank:=@rank=1 AS rank, FK_ID
FROM TABLE_1 ORDER BY num_1 DESC

Then insert the contents of the temporary table into TABLE_2.

INSERT INTO TABLE_2 (FK_ID, Rank_Number_1, Rank_Number_2, Rank_Number_3)
	SELECT FK_ID, max(Rank_Number_1), max(Rank_Number_2), max(Rank_Number_3)
	FROM TABLE_A
	GROUP BY FK_ID;

Finally, drop your temporary table.

DROP TEMPORARY TABLE TABLE_A;

Tags:
No comments yet.

Leave a Comment

*