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

Mysql – Order of column values to new column value

I have a mysql table as:

A B C D E F
12 23 15 17 9 21
9 13 15 19 4 2
26 11 6 3 15 21

I want to add a new column, where the value is concatenation of the 6 column names, in descending order of the values, like this:

code
BFDCAE
DCBAEF
AFEBCD

I tried using GREATEST() and LEAST() but that leads to at least 6!=720 lines of code, which I want to avoid. Is there a simple way to achieve this?

Tags:

2

Answers


  1. 0

    Here is how to do it using UNION ALL and GROUP_CONCAT :

    SELECT A, B, C, D, E, F, GROUP_CONCAT(col_id ORDER BY col_value DESC) AS CODE
FROM (
  SELECT *, A as col_value, 'A' col_id
  FROM mytable
  UNION ALL 
  SELECT *, B, 'B' 
  FROM mytable
  UNION ALL 
  SELECT *, C, 'C'
  FROM mytable
  UNION ALL 
  SELECT *, D, 'D'
  FROM mytable
  UNION ALL 
  SELECT *, E, 'E'
  FROM mytable
  UNION ALL 
  SELECT *, F, 'F'
  FROM mytable
) AS s
GROUP BY A  ,B, C,  D,  E,  F;

    Results :

    A   B   C   D   E   F   CODE
9   13  15  19  4   2   D,C,B,A,E,F
12  23  15  17  9   21  B,F,D,C,A,E
26  11  6   3   15  21  A,F,E,B,C,D

    Demo here

    Login or Signup to reply.
  2. 0 
    WITH 
cte1 AS ( -- add a column with unique value
  SELECT *, ROW_NUMBER() OVER () rn
  FROM test
  ),
cte2 (rn, val, col) AS ( -- normalize data
  SELECT rn, A, 'A' FROM cte1 UNION ALL
  SELECT rn, B, 'B' FROM cte1 UNION ALL
  SELECT rn, C, 'C' FROM cte1 UNION ALL
  SELECT rn, D, 'D' FROM cte1 UNION ALL
  SELECT rn, E, 'E' FROM cte1 UNION ALL
  SELECT rn, F, 'F' FROM cte1
  )
SELECT GROUP_CONCAT(col ORDER BY val DESC, col ASC SEPARATOR '') code
FROM cte2
GROUP BY rn;
    code
    BFDCAE
    DCBAEF
    AFEBCD

    fiddle

    If the table is more wide than provided one, and primary key exists in it, then it must be used instead of rn, and cte1 must be removed.

    Additional col ASC in ORDER BY makes the output deterministic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search