So I have a single record row; and it is like this:
data1 data2 data3 data4
4 3 1 2
Now I want to sort the data's, in 1 2 3 4 order.
So: data3, data4, data2, data1 for row 1
Would anyone know how to sort within a record this way?
Here are two methods. Here is the unpivot and repivot method:
select substring_index(group_concat(col order by col), ',', 1) as data1,
substring_index(substring_index(group_concat(col order by col), ',', 2), ',', -1) as data2,
substring_index(substring_index(group_concat(col order by col), ',', 3), ',', -1) as data3,
substring_index(substring_index(group_concat(col order by col), ',', 4), ',', -1) as data4
from ((select data1 as col from table) union all
(select data2 as col from table) union all
(select data3 as col from table) union all
(select data4 as col from table)
) t
group by col1, col2, col3, col4; # A real id would be better for this
One option is to create a simple function:
With that function created, you can do something like this:
(Just replace the inline view
t
with a reference to your table that contains columns data1..data4)SQL Fiddle demonstration: http://sqlfiddle.com/#!9/e0e39/2