I have a table that listed six unique numbers in a row, on their specified columns:

How I can use MySQL to get the row values in an ascending order?

Thank you!

I tried ORDER BY FIELD(), subqueries and concatenation, but nothing works.

SELECT aa.*, 
(SELECT CONCAT(n1,",",n2,",",n3,",",n4,",",n5,",",n6) FROM table bb WHERE bb.id=aa.id ORDER BY FIELD(n1,n2,n3,n4,n5,n6) asc) AS conc 
FROM table aa 
WHERE aa.id=1

I know is a childish approach, but I have no idea how to get the right result.

CodePudding user response：

SELECT id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 1), ',', -1) AS n1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 2), ',', -1) AS n2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 3), ',', -1) AS n3,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 4), ',', -1) AS n4,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 5), ',', -1) AS n5,
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 6), ',', -1) AS n6
FROM (
    SELECT id, n1 AS n FROM table
    UNION ALL
    SELECT id, n2 AS n FROM table
    UNION ALL
    SELECT id, n3 AS n FROM table
    UNION ALL
    SELECT id, n4 AS n FROM table
    UNION ALL
    SELECT id, n5 AS n FROM table
    UNION ALL
    SELECT id, n6 AS n FROM table
    ) AS unpivotted
GROUP BY id

CodePudding user response：

Try this, but you should care if they have the same number

select
id,
case when n1 < n2 and n1 < n3 then n1 
    else case when n2 < n3 and n2 < n1 then n2
    else case when n3 < n2 and n3 < n1 then n3
    end end end as n1,
... as n2,
... as n3
from table 
where id = 1