CODEWITHSUNDEEP
mysqlsql
JC_BI
JC_BI

Reputation: 419

Retrieve top 3 values from a row with fields name MySql

This is how my table looks like:

ID    USER    VALUE1   VALUE2   VALUE3   VALUE4    VALUE5
2TG   Admin     20       45       10       12        24
5HT   Supp      35       10       35       18        31
8PK   Exc       80       62       01       38        70
1JH   MANG      48       96       21       23        36
4YT   LAW       58       50       29       58        51

The code that I'm using:

SELECT ID, USER,  MAX(MAX1) AS MAX, ValueName FROM ( SELECT ID, USER,Value1 AS MAX1, 'Value1' as ValueName FROM Data

UNION
SELECT ID,USER, Value2 AS MAX1, 'Value2' as ValueName
FROM Data 
UNION
SELECT ID, USER,Value3 AS MAX1, 'Value3' as ValueName
FROM Data
UNION
SELECT ID, USER,Value4 AS MAX1, 'Value4' as ValueName
FROM Data
UNION
SELECT ID, USER,Value5 AS MAX1, 'Value5' as ValueName
FROM Data) T  GROUP BY ID

With the code described above this is the output that I'm getting:

ID    USER    MAX1   ValueName
2TG   Admin    45    Value1
5TH   Supp     35    Value1
8PK   Exc      80    Value1
1JH   MANG     96    Value1
4YT   LAW      58    Value1

And this would be the desired output:

 ID    USER    MAX1   MAX2   MAX3   ValueName1  ValueName2   ValueName3
 2TG   Admin    45     24     20    Value2      Value5       Value1 
 5TH   Supp     35     35     31    Value1      Value3       Value5
 8PK   Exc      80     70     62    Value1      Value5       Value2
 1JH   MANG     96     48     36    Value2      Value1       Value5
 4YT   LAW      58     58     51    Value1      Value4       Value5

What I'm trying to do is get the maximum 1(Highest), Maximum 2 (Second Highest) and the Maximum 3 (Third Highest) value across the row along with the name of the column where the 1,2,3 maximum value was found or belong to.

Any help will be appreciate.

Upvotes: 2

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

If you can accept a slightly different result format, you can use group_concat():

SELECT ID, USER,
       SUBSTRING_INDEX(GROUP_CONCAT(max1 ORDER BY max1 DESC) , ',', 3) as Top3_names,
       SUBSTRING_INDEX(GROUP_CONCAT(ValueName ORDER BY max1 DESC) , ',', 3) as Top3_names,
FROM (SELECT ID, USER, Value1 AS MAX1, 'Value1' as ValueName FROM Data
      UNION ALL
      SELECT ID,USER, Value2 AS MAX1, 'Value2' as ValueName
      FROM Data 
      UNION
      SELECT ID, USER,Value3 AS MAX1, 'Value3' as ValueName
      FROM Data
      UNION
      SELECT ID, USER,Value4 AS MAX1, 'Value4' as ValueName
      FROM Data
      UNION
      SELECT ID, USER,Value5 AS MAX1, 'Value5' as ValueName
      FROM Data
     ) T
GROUP BY ID;

Actually, you can then parse out the data you want:

SELECT ID, USER,
       SUBSTRING_INDEX(GROUP_CONCAT(max1 ORDER BY max1 DESC), ',', 1) as max1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(max1 ORDER BY max1 DESC), ',', 2), ',', -1) as max2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(max1 ORDER BY max1 DESC), ',', 3), ',', -1) as max3,
       SUBSTRING_INDEX(GROUP_CONCAT(valuename ORDER BY max1 DESC), ',', 1) as valuename1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(valuename, ORDER BY max1 DESC), ',', 2), ',', -1) as valuename2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(valuename, ORDER BY max1 DESC), ',', 3), ',', -1) as valuename3
FROM . . .

There is another method using variables, but this method is probably the simplest, if you don't' have window functions.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

In MySQL 8.x you can do:

with x as (
  select id, Value1 as val, 'Value1' as col from data
  union all select id, Value2, 'Value2' from data
  union all select id, Value3, 'Value3' from data
  union all select id, Value4, 'Value4' from data
  union all select id, Value5, 'Value5' from data
),
y as (
  select id, val, col,
    row_number() over (partition by id order by val desc) as rn
  from x
)
select
  d.id, d.user,
  v1.val as max1, v1.col as ValueName1,
  v2.val as max2, v2.col as ValueName2,
  v3.val as max3, v3.col as ValueName3
from data d
join (
  select y.id, y.val, y.col
  from y join data d on d.id = y.id
  where rn = 1
) v1 on v1.id = d.id
join (
  select y.id, y.val, y.col
  from y join data d on d.id = y.id
  where rn = 2
) v2 on v2.id = d.id
join (
  select y.id, y.val, y.col
  from y join data d on d.id = y.id
  where rn = 3
) v2 on v2.id = d.id

Upvotes: 2

Related Questions