MySQL - How to sort within a record?

Question

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?

Answer 1

One option is to create a simple function:

DELIMITER $$

CREATE FUNCTION get_nth(vn INT, v1 INT, v2 INT, v3 INT, v4 INT)
RETURNS INT
BEGIN
    DECLARE v_offset INT;
    DECLARE v_return INT;
    SET v_offset = vn-1; 
    SELECT o.val INTO v_return
      FROM ( SELECT v1 AS val
              UNION ALL SELECT v2 
              UNION ALL SELECT v3 
              UNION ALL SELECT v4 
              ORDER BY 1
              LIMIT v_offset,1
            ) o;
   RETURN v_return;
END$$

DELIMITER ;

With that function created, you can do something like this:

SELECT get_nth(1,t.data1,t.data2,t.data3,t.data4) AS od1
     , get_nth(2,t.data1,t.data2,t.data3,t.data4) AS od2
     , get_nth(3,t.data1,t.data2,t.data3,t.data4) AS od3
     , get_nth(4,t.data1,t.data2,t.data3,t.data4) AS od4
  FROM ( 
         SELECT 4 AS data1
              , 3 AS data2
              , 1 AS data3
              , 2 AS data4
       ) t

(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

Answer 2

As soon you have columns data1, data2 etc. you need to change your database model. Mosttimes it means that you need a new extra table.

wrong:

users:
- user_id
- name
- address1
- address2
- address3

right:

users:
- user_id
- name

addresses:
- address_id
- user_id
- address

Answer 3

You can run the following code. SELECT * FROM table_name ORDER BY coloumn_name ASC;

You can also ignore ASC, as it means arranging in an Ascending order.

Answer 4

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