Get column name which has the max value in a row sql

Question

I have a a table in my database where I store categories for newsarticles and each time a user reads an article it increments the value in the associated column. Like this:

Now I want to execute a query where I can get the column names with the 4 highest values for each record. For example for user 9, it would return this:

I've tried several things, searched a lot but don't know how to do it. Can anyone help me?

Answer 1

This should do it:

select
  userid,
  max(case when rank=1 then name end) as `highest value`,
  max(case when rank=2 then name end) as `2nd highest value`,
  max(case when rank=3 then name end) as `3rd highest value`,
  max(case when rank=4 then name end) as `4th highest value`
from
(
  select userID, @rownum := @rownum + 1 AS rank, name, amt from (
    select userID, Buitenland as amt, 'Buitenland' as name from newsarticles where userID = 9 union
    select userID, Economie, 'Economie' from newsarticles where userID = 9 union
    select userID, Sport, 'Sport' from newsarticles where userID = 9 union
    select userID, Cultuur, 'Cultuur' from newsarticles where userID = 9 union
    select userID, Wetenschap, 'Wetenschap' from newsarticles where userID = 9 union
    select userID, Media, 'Media' from newsarticles where userID = 9
  ) amounts, (SELECT @rownum := 0) r
  order by amt desc
  limit 4
) top4
group by userid

Demo: http://www.sqlfiddle.com/#!2/ff624/11

Answer 2

A very simple way of doing this is shown below

select userId, substring_index(four_highest,',',1) as 'highest value', substring_index(substring_index(four_highest,',',2),',',-1) as '2th highest value',  substring_index(substring_index(four_highest,',',3),',',-1) as '3 rd highest value',  substring_index(four_highest,',',-1) as '4th highest value'   from
(
select userid, convert(group_concat(val) using utf8) as four_highest from
(
select userId,Buitenland as val,'Buitenland' as col from test where userid=9 union
select userId,Economie as val,' Economie' as col from test where   userid=9 union
select userId,Sport as val ,'Sport' as col from test where  userid=9 union
select userId,Cultuur as val,'Cultuur' as col from test where userid=9 union
select userId,Wetenschap as val,'Wetenschap' as col from test where userid=9 union
select userId,Media as val,'Media' as col from test where  userid=9 order by val desc limit 4
) inner_query
)outer_query;

Answer 3

PL/SQL, maybe? Set user_id, query your table, store the returned row in an nx2 array of column names and values (where n is the number of columns) and sort the array based on the values.

Of course, the correct thing to do is redesign your database in the manner that @octern suggests.

Answer 4

This will get you started with the concept of grabbing the highest value from multiple columns on a single row (modify for your specific tables - I created a fake one).

create table fake 
(
  id int Primary Key,
  col1 int,
  col2 int,
  col3 int,
  col4 int 
)
insert into fake values (1, 5, 9, 27, 10)
insert into fake values (2, 3, 5, 1, 20)
insert into fake values (3, 89, 9, 27, 6)
insert into fake values (4, 17, 40, 1, 20)

SELECT *,(SELECT Max(v) 
FROM (VALUES (col1), (col2), (col3), (col4) ) AS value(v))
FROM fake