CODEWITHSUNDEEP
mysql
Akeshwar Jha
Akeshwar Jha

Reputation: 4576

MySQL: Print the name of the rows with top 3 values of the each column

+-----------------------------+------+------+------+
| State                       | 2006 | 2007 | 2008 |
+-----------------------------+------+------+------+
| Andaman and Nicobar Islands |   32 |   27 |   23 |
| Andhra Pradesh              | 3824 | 2432 | 1591 |
| Arunachal Pradesh           |   12 |    9 |   25 |
| Assam                       |  617 |  319 |  530 |
| Bihar                       | 1665 | 1949 | 1944 |
| Chandigarh                  |    0 |    5 |    4 |
| Chhattisgarh                |  374 |  401 |  855 |
| Dadra and Nagar Haveli      |    0 |    0 |    0 |
| Daman and Diu               |    2 |    0 |    1 |
| Delhi                       |    0 |    0 |    0 |
| Goa                         |   72 |    1 |   42 |
| Gujarat                     | 2038 |  328 |  540 |
| Haryana                     |  350 |  520 |  427 |
| Himachal Pradesh            |  323 |  214 |   34 |

I have the similar table, just with more number of rows. The columns depict the number of accidents for the given year. I need to print the top 3 states with most number of accidents EVERY YEAR. Is there a way to do it in one go? Currently, I'm only able to do this year-wise:

SELECT State AS 'Accidents-2006' 
FROM accidents 
ORDER BY `2006` 
DESC LIMIT 3;

And then repeating it for 2007 and 2008.

EDIT: I'm searching for an output like this:

+-----------------+-----------------+--------------------+
| 2006            | 2007            | 2008               |
+-----------------+-----------------+--------------------+
| Andhra Pradesh  | Andhra Pradesh  | Bihar              |
| Gujarat         | Bihar           | Andhra Pradesh     |
| Bihar           | Haryana         | Chhattisgarh       |

Upvotes: 0

Views: 66

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You table has some serious normalization issues.

Having said that and in case restructuring your table schema is not an option for you, here's a way to get the required result set using variables:

SELECT `Y2006`, `Y2007`, `Y2008`
FROM (
  SELECT State AS `Y2006`, @rn1:=@rn1+1 AS rn1
  FROM mytable
  CROSS JOIN (SELECT @rn1:=0) AS v
  ORDER BY `2006` DESC LIMIT 3 
) t1
LEFT JOIN (  
   SELECT State AS `Y2007`, @rn2:=@rn2+1 AS rn2
   FROM mytable
   CROSS JOIN (SELECT @rn2:=0) AS v
   ORDER BY `2007` DESC LIMIT 3 
) t2 ON t1.rn1 = t2.rn2   
LEFT JOIN (
   SELECT State AS `Y2008`, @rn3:=@rn3+1 AS rn3
   FROM mytable
   CROSS JOIN (SELECT @rn3:=0) AS v
   ORDER BY `2008` DESC LIMIT 3
) t3 ON t2.rn2 = t3.rn3

Demo here

Upvotes: 1

William
William

Reputation: 11

Note: Probably should be a comment but not enough Rep to comment.

Personally I would change it to be 3 (4 for an ID) column table consisting of:

State                        | Year | Accidents
Andaman and Nicobar Islands  | 2006 | 32
Andaman and Nicobar Islands  | 2007 | 27

This would be easier for future proofing the data as you would not need to edit the structure to add another year of data and will also help you arrange the data how you want it.

Upvotes: 1

Related Questions