MySQL Select unique sorted field values

Question

I've trying to concatenate the values of 2 GROUP_CONCAT( columns ) from a single table that's been joined twice, then get the unique items from the list.

I can do all this outside of my query but if possible it would be nice to just pull the data from the DB with a JOIN and some fancy string manipulation.

Simply put, I want to produce 1,2,3,4 from selecting 1,2,3 and 1,3,4. The 1,2,3 adn 1,3,4 are the results of the GROUP_CONCAT on the twice joined table. I can get this far:

SELECT CONCAT_WS(
    ",",
    "1,2,3", 
    "1,3,4"
)

Which outputs 1,2,3,1,3,4

I'd like to be able to do something like:

-- NOTE TO SKIM READERS: THIS QUERY WILL NOT WORK
SELECT 
    SORT_LIST( 
        DISTINCT
        CONCAT_WS(
            ",",
            "1,2,3", 
            "1,3,4"
        )
    )
-- NOTE TO SKIM READERS: THIS QUERY WILL NOT WORK

But I can't find anything like that in MySQL.

The 1,2,3 and 1,3,4 have already been produced with GROUP_CONCAT( DISTINCTcol)

Do you have comma separated values in a single column? There are better ways of storing this data that would make this query more straightforward. Is that an option? — Mark Bolusmjak
– Mark Bolusmjak, Commented May 13, 2015 at 13:17
I'm joining to another table, that has values A=1,B=2 and B=1,A=2 in different rows. So I'm joining twice and selecting CONCAT( A,B ) then CONCAT( B,A ), then CONCAT the results which is giving me "12,12". The sort aspect of the question is less important, it's really the unique/distinct aspect I need to work out — Pete
– Pete, Commented May 13, 2015 at 13:20
"1,2,3" and "1,3,4" are two string and they doesn't match, you want to treat them integer 1,2,3 and 1,3,4, that i think is not possible without writting a custom function. — developerCK
– developerCK, Commented May 13, 2015 at 13:38
Ok I've figured out a way around it, I'll have to fully select all rows in my 2 tables in a UNION within a subquery, and then join on an ID to limit them but it means I can use a GROUP_CONCAT( DISTINCT... ) on the result of the subquery, which gives me what I want: pastebin.com/NTh2CfuD — Pete
– Pete, Commented May 13, 2015 at 13:46

Pete · Accepted Answer · 2015-05-15 08:09:30Z

1

As stated in my comment I worked out a way to achieve distinct concatenated lists of strings using a sub query:

DROP TABLE IF EXISTS `test1234`;
CREATE TABLE `test1234` (
    `val` int(1),
    `type` varchar(1)
);

INSERT INTO `test1234` VALUES
( 1, 'a' ),
( 2, 'a' ),
( 3, 'a' ),
( 1, 'b' ),
( 3, 'b' ),
( 4, 'b' );

SELECT GROUP_CONCAT( `val` ) AS `vals`
FROM (
        (
                SELECT `val` FROM `test1234` WHERE `type` = 'a'
        ) UNION DISTINCT (
                SELECT `val` FROM `test1234` WHERE `type` = 'b'
        )
) AS `test`;
DROP TABLE IF EXISTS `test1234`;

This selected 1,2,3,4

answered May 15, 2015 at 8:09

Pete

4,6329 gold badges48 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Select unique sorted field values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related