CODEWITHSUNDEEP
phpmysql
Armar King
Armar King

Reputation: 13

PHP Top 5 numbers in the same row in mysql

How to get the first highest numbers in the same row in mysql for example i have this row

I have this database 

Name Level1 Level2 Level3 Level4 Level5 Level6 Level7 Level8 Level9 Level10 Level11 Level12
armar 80 50 30 60 80 60 90 35 90 66 55 22

ok i want to get the top 5 levels in that row in mysql and order from higher to lower

example result will be : 90 90 80 80 60

Thanks!

Upvotes: 1

Views: 98

Answers (2)

Eldar
Eldar

Reputation: 591

It is better to do this with PHP this will give you the best performance, Once you fetch the row with PHP and have the row as an array,

$stack = array();
for($i=1;$i<=12;$i++){
    $stack[] = $row["Level".$i];
}
array_reverse(sort($stack));

Now $stack[0], $stack[1] ... $stack[4] will give you the highest numbers.

Upvotes: 1

fthiella
fthiella

Reputation: 49049

A PHP solution would be better here, but if you just need a MySQL query you could use this:

SELECT
  Name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 1), ',', -1) Top1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 2), ',', -1) Top2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 3), ',', -1) Top3,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 4), ',', -1) Top4,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 5), ',', -1) Top5
FROM (
  SELECT Name, GROUP_CONCAT(Level ORDER BY Level DESC) Levels
  FROM (
    SELECT Name, Level1 Level FROM yourtable
    UNION ALL
    SELECT Name, Level2 FROM yourtable
    UNION ALL
    SELECT Name, Level3 FROM yourtable
    UNION ALL
    SELECT Name, Level4 FROM yourtable
    UNION ALL
    SELECT Name, Level5 FROM yourtable
    UNION ALL
    SELECT Name, Level6 FROM yourtable
    UNION ALL
    SELECT Name, Level7 FROM yourtable
    UNION ALL
    SELECT Name, Level8 FROM yourtable
    UNION ALL
    SELECT Name, Level9 FROM yourtable
    UNION ALL
    SELECT Name, Level10 FROM yourtable
    UNION ALL
    SELECT Name, Level11 FROM yourtable
    UNION ALL
    SELECT Name, Level12 FROM yourtable
  ) s
  GROUP BY Name
) s

Please see fiddle here.

Upvotes: 0

Related Questions