CODEWITHSUNDEEP
mysqlsql
Eric Miller
Eric Miller

Reputation: 21

Select ID for each max column in a single mysql table

My table ### inf_simpleranks

+-----+--------------+----------+---------+----------+
| uid | style_normal | style_sw | style_w | style_ad |
+-----+--------------+----------+---------+----------+
|   1 |          159 |        9 |     164 |      195 |
|   2 |           46 |       39 |      55 |      159 |
|   3 |          188 |       28 |     171 |      174 |
|   4 |          135 |       32 |     151 |       63 |
|   5 |            3 |      156 |     173 |      197 |
+-----+--------------+----------+---------+----------+

I can get the max value for each column

select 
  a.uid,
  a.style_normal
from
  inf_simpleranks a
  inner join (
    select max(style_normal) as style_normal
    from inf_simpleranks
  ) b on a.style_normal = b.style_normal;


+-----+--------------+
| uid | style_normal |
+-----+--------------+
|   3 |          188 |
+-----+--------------+

And this 

+-----+----------+
| uid | style_sw |
+-----+----------+
|   5 |      156 |
+-----+----------+

Sometimes the UID would match

+-----+----------+
| uid | style_ad |
+-----+----------+
|   5 |      197 |
+-----+----------+

But I am trying to get it into a single query so it would look like this:

+----------------+--------------+
| MAX            | UID          |
+----------------+--------------+
| style_normal   | 3            |
| style_sw       | 5            |
| style_w        | 5            |
| style_ad       | 5            |
+----------------+--------------+

Upvotes: 2

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think union all might be the best approach:

(select 'style_normal' as which, style_normal, uid
 from inf_simpleranks
 order by style_normal desc
 limit 1
) union all
(select 'style_sw' as which, style_sw, uid
 from inf_simpleranks
 order by style_sw desc
 limit 1
) union all
. . .

If your data is not too big (due to internal constraints on group_concat() and you can have the values in columns, you can do:

select substring_index(group_concat(uid order by style_normal desc), ',', 1) as style_normal,
       substring_index(group_concat(uid order by style_sw desc), ',', 1) as style_sw,
       . . .
from inf_simpleranks;

Upvotes: 3

Related Questions