Arda Ilgaz Arda Ilgaz - 1 month ago 5
MySQL Question

MySQL : select 3 column names with greatest values from a row

I am quite new to MySQL and have following problem:

I need to select 3 column names with the greatest values, whereas my "countries" table is structured like this:

countryCode tag1 tag2 tag3 tag4 tag5 tag6 tag7 tag8

GB 2 4 4 8 9 1 1 0
.
.
.


So the query should return tag5, tag4, tag3 in this case.
I messed with select max and union, but did not succeed yet.

Any help is appreciated :)

Further explanation

countryCode backpack beach culture forest mountains scuba trailer
AD 0 5 85 1 5 6 7
AE 7 2 15 1 9 6 7
.
.
.


I am querying from PHP, taking the countryCode as Post. I need to get the result of the columns with the hightest 3 numbers. For this example; if I query AD, it should return culture, trailer, scuba

Answer

I'm not sure I understand your question fully, so I added to it a little - supposed I have this input:

countryCode   tag1   tag2   tag3   tag4   tag5   tag6   tag7   tag8

     GB        2      4      5       8      9      1      1     0 
     FR        9      4      4       8      9      1      7     0 
     .
     .

Then you want the output to be: countryCode number1 number2 number3

     GB        tag5           tag4      tag3       
     FR        tag1, tag5     tag4      tag7

?

If you expect the above result, max will not help you and the format of your data is not helping. You could possibly do something like the below, which is all sorts of bad...

select countryCode, tagName, value from 
(select a.countryCode, 'tag1'  tagName, a.tag1  value from countries a where a.countryCode = 'GB' UNION
select  b.countryCode, 'tag2'  tagName, b.tag2  value from countries b where b.countryCode = 'GB' UNION
select  c.countryCode, 'tag3'  tagName, c.tag3  value from countries c where c.countryCode = 'GB' UNION
select  d.countryCode, 'tag4'  tagName, d.tag4  value from countries d where d.countryCode = 'GB' UNION
select  e.countryCode, 'tag5'  tagName, e.tag5  value from countries e where e.countryCode = 'GB' UNION
select  f.countryCode, 'tag6'  tagName, f.tag6  value from countries f where f.countryCode = 'GB' UNION
select  g.countryCode, 'tag7'  tagName, g.tag7  value from countries g where g.countryCode = 'GB' UNION
select  h.countryCode, 'tag8'  tagName, h.tag8  value from countries h where h.countryCode = 'GB' UNION
select  i.countryCode, 'tag9'  tagName, i.tag9  value from countries i where i.countryCode = 'GB' UNION
select  j.countryCode, 'tag10' tagName, j.tag10 value from countries j where j.countryCode = 'GB' ) 
as results
order by value desc
limit 3;

EDIT: Just a sample of the data and table structure I've got. I'm running this in MySQL 5.5.44-0ubuntu0.12.04.1, just in case this is a version difference. So if you adjust my sample query above, that should work.

mysql> desc countries;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| countryCode | varchar(2) | YES  |     | NULL    |       |
| tag1        | int(11)    | YES  |     | NULL    |       |
| tag2        | int(11)    | YES  |     | NULL    |       |
| tag3        | int(11)    | YES  |     | NULL    |       |
| tag4        | int(11)    | YES  |     | NULL    |       |
| tag5        | int(11)    | YES  |     | NULL    |       |
| tag6        | int(11)    | YES  |     | NULL    |       |
| tag7        | int(11)    | YES  |     | NULL    |       |
| tag8        | int(11)    | YES  |     | NULL    |       |
| tag9        | int(11)    | YES  |     | NULL    |       |
| tag10       | int(11)    | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> select * from countries;
+-------------+------+------+------+------+------+------+------+------+------+-------+
| countryCode | tag1 | tag2 | tag3 | tag4 | tag5 | tag6 | tag7 | tag8 | tag9 | tag10 |
+-------------+------+------+------+------+------+------+------+------+------+-------+
| GB          |    5 |    1 |    2 |    5 |    7 |    8 |    9 |    3 |   15 |    22 |
| FR          |    5 |   14 |    2 |    5 |    7 |    8 |    9 |    3 |   15 |    22 |
+-------------+------+------+------+------+------+------+------+------+------+-------+
2 rows in set (0.00 sec)

Ideally though, if you are getting this data somewhere else, it maybe easier and more recommended to use the other table, as the query I've sent you will be slow.

The ideal scenario is that you have a table Country Code and Tag that you could use an aggregate function directly on it.

Comments