## MySQL: Ranking applied over Multiple Columns

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)`

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;`