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?

You can't do that, however you can transform the table using unpivot method http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table and then sort the results. — fsacer, Jun 07 '15 at 11:56
I'm sensing a very high probability that you're not making use of relational table structures... — David, Jun 07 '15 at 11:56
Recommendations: 1) don't do this in SQL; do it on the client instead. 2) reconsider the table design... maybe the values stored in separate columns of the same row would be better stored as rows in a separate table. — spencer7593, Jun 07 '15 at 13:45

score 0 · Answer 1 · answered Jun 07 '15 at 11:58

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

score 0 · Answer 2 · answered Jun 07 '15 at 12:01

0

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.

answered Jun 07 '15 at 12:01

praveenbharatsagar

53
7

No I dont want to sort vertically, I wanna sort horizontally – QuestionMaker Jun 07 '15 at 12:08
Ok. Then you can refer the answer to your question of Gordon Linoff. – praveenbharatsagar Jun 07 '15 at 12:58

score 0 · Answer 3 · answered Jun 07 '15 at 12:20

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

score 0 · Answer 4 · answered Jun 07 '15 at 14:10

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

MySQL - How to sort within a record?

4 Answers4