George Garchagudashvili George Garchagudashvili - 3 months ago 8
MySQL Question

Order column values from the selected row

I've a table where I've 4 columns int type.

col1 col2 col3 col4
3 2 3 4
2 2 4 3


And I'm trying to select those values in ordered form like:

colx colx colx colx
2 3 3 4


Column names does not matter in result, also having result as one col is welcomed (if concatenated in some fashion):

colx
2.3.3.4


To be true I couldn't try anything except googling but got nothing.

Thanks

Answer

For single row it could be done with the current structure however if you have multiple rows you need a primary or any unique key.

Consider the following

mysql> select * from test ;
+------+------+------+------+------+
| col1 | col2 | col3 | col4 | id   |
+------+------+------+------+------+
|    3 |    2 |    3 |    4 |    1 |
|    4 |    7 |    1 |    3 |    2 |
+------+------+------+------+------+

Now the query would be as

select 
id,
group_concat(x.col order by x.col separator '.') as colx
from ( 
  select id,col1 as col from test 
  union all 
  select id,col2 as col from test 
  union all 
  select id,col3 as col from test 
  union all 
  select id,col4 as col from test
)x group by id

The result will look like

+------+---------+
| id   | colx    |
+------+---------+
|    1 | 2.3.3.4 |
|    2 | 1.3.4.7 |
+------+---------+
Comments