Question 21078 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I get an ordered list of columns values in a row in MySQL?

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

id n1 n2 n3 n4 n5 n6
1 44 11 32 14 28 19

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

id n1 n2 n3 n4 n5 n6
1 11 14 19 28 32 44

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.

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search