Sort a field of array upon mysql query

Question

I have a field: userids which contains 19,2,23 for example, which I populate based on user ids added to a room arbitrarily. I list the corresponding names based on these userids.

Right now I get Richard, Evan, James from this query (19 = Richard, 2 = Evan, 23 = James).

What I would like to do is sort the array (the field) upon query so that it is 2,19,23 and the names are returned Evan, Richard, James.

So a query that looks like this:

    SELECT c.*,c.NAME as PAGE, ASORT(c.USERIDS) as USERIDS, p.* 
    FROM TBL_CONTENT c, TBL_PAGE p 
    WHERE c.PAGEID = p.ID AND FIND_IN_SET(?, c.USERIDS)

Should look like (help me with this query)?

of course, the ASORT function does not exist - I just include it as an example of what I am trying to do.

Any help here? Thank you!

You shouldn't put comma-separated lists in table columns in the first place. If you had it in a separate table you could use `GROUP_CONCAT` and its `ORDER BY` option. — Barmar, May 14 '20 at 19:42
Because people have beenmaking this mistake for years and MySQL chose to accomodate them. — Barmar, May 14 '20 at 19:48
But there's a very limited set of things you can do with them, and sorting is not one of them. — Barmar, May 14 '20 at 19:49
ok, thanks for that. Perhaps I will just live with the default sort - by members of the room in order of joining — richardwhitney, May 14 '20 at 19:51
Why not normalize your table? That way you won't constantly run into problems like this. — Honeyboy Wilson, May 14 '20 at 20:17

nbk · Answer 1 · 2020-05-14T21:20:07.883

This works with mysql 8

For mysql 5.x , you need much more code to do that

SET @a = '19,2,23';

SELECT GROUP_CONCAT(ord ORDER BY ord) 
FROM
(select  CAST(num as UNSIGNED) ord
from (SELECT @a as names) t
join json_table(
  replace(json_array(t.names), ',', '","'),
  '$[*]' columns (num varchar(50) path '$')
) j) z1

RESULTS in

# GROUP_CONCAT(ord ORDER BY ord)
2,19,23

For Mysql 5.x You can use a function

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_stringnumbers_and_sort`(_text TEXT) RETURNS text CHARSET utf8mb4
    DETERMINISTIC
BEGIN
SET @text = _text;
SELECT 
GROUP_CONCAT((name+0) ORDER BY (name +0)) INTO @output
FROM
(
select
  SUBSTRING_INDEX(SUBSTRING_INDEX(t1.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN (SELECT @text as name) t1
  on CHAR_LENGTH(t1.name)
     -CHAR_LENGTH(REPLACE(t1.name, ',', ''))>=numbers.n-1
order by  n
) t2;

RETURN @output;
END$$
DELIMITER ;

SO that this query

SET @a = '19,2,23';
SELECT fn_split_stringnumbers_and_sort(@a);

has the same result

# fn_split_stringnumbers_and_sort(@a)
2,19,23

Caution This function only would split 5 comma separated numbers if you have more than that you have to increase table with more UNION

no JSON_Table works only in mysql 8 but you can use the new function i programmed, please see the causion that this function only sorts 5 numkbers, if you have more add more unions in the table numbers — nbk, May 14 '20 at 21:21

Sort a field of array upon mysql query

1 Answers1