Order column values from the selected row

Question

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

Thanks

Your question is not clear. You want information from ascending order or descending order? is that is your question? — PeakGen, Aug 18 '16 at 08:18
It's clear instead, cuz I showed what I wanted, but not matter ascending or descending any case is welcomed — George G, Aug 18 '16 at 08:19
You want to make sorting based on columns and not rows right? Afaik this is not something SQL deals with but you can pivot them check this question: http://stackoverflow.com/questions/13944417/mysql-convert-column-to-row-pivot-table — Fma, Aug 18 '16 at 08:19
@Fma I don't want piloting just want to order values in rows — George G, Aug 18 '16 at 08:21
@RiggsFolly it's one row level but will need for multiple rows for sure — George G, Aug 18 '16 at 08:21
@GeorgeGarchagudashvili I don't think you can do otherwise because SQL deals with rows and designed to work like that. It is probably better to to it manually in another language or use a pivot table and return its result as a new row. — Fma, Aug 18 '16 at 08:22
Even if you did do this, how would the data returned be of any use as you no longer know what column is in what order. Surely the data becomes meaningless — RiggsFolly, Aug 18 '16 at 08:23
What is the database type. This matters because the approach to follow and syntax will differ by dbms. If tsql you could unpivot, sort those rows, then concatenate. — Paul Maxwell, Aug 18 '16 at 08:25
Sorry i now see the mysql tag. Apologies. ... Yep. Just saw it. — Paul Maxwell, Aug 18 '16 at 08:29
I want to select unique number sets. I mean 3 2 1 3 is same as 2 3 3 1 — George G, Aug 18 '16 at 08:31

score 3 · Accepted Answer · edited Aug 18 '16 at 08:36

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 |
+------+---------+

Order column values from the selected row

1 Answers1