FIND_IN_SET with comma seperated values for sorting condition

Question

I am using find_in_set for fetching data using below query and it works correctly.

SELECT * 
FROM A 
WHERE FIND_IN_SET(
column1,
(
 SELECT column1 
 FROM B 
 WHERE  id = 21)
);

Here this query SELECT column1 FROM B WHERE id = 21 gives result like '175587,282329' but I want that '175587,282329' whichever highest value will come here that I should use.In this case,It would be 282329.but Is will any number of comma seperated values. Thanks

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Aug 09 '14 at 09:54
@Strawberry,I have put just simple form of my query and I am using subquery in second condtion.Maybe it is sufficient — Deval Shah, Aug 09 '14 at 09:55
@Strawberry,I have inserted data from API so its not possible that time — Deval Shah, Aug 09 '14 at 10:03
I'm sorry for you, because others have made a bad decision. Your problem could be rewritten to: *How do I get the greatest value of an comma separated list*. I think you'll have to do some tedious string parsing to do. — VMai, Aug 09 '14 at 10:28

score 1 · Answer 1 · answered Aug 09 '14 at 11:03

While I don't seriously advocate this as a solution, consider the following hack...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,string VARCHAR(20) NOT NULL UNIQUE
 );

 INSERT INTO my_table(string) VALUES
 ('1,2,3'),
 ('2,3,4'),
 ('3,4,5'),
 ('1,3,5'),
 ('2,4,6');

 SELECT * FROM my_table;
 +----+--------+
 | id | string |
 +----+--------+
 |  1 | 1,2,3  |
 |  4 | 1,3,5  |
 |  2 | 2,3,4  |
 |  5 | 2,4,6  |
 |  3 | 3,4,5  |
 +----+--------+

 SELECT * FROM ints;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT x.*
      , GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(string,',',i+1),',',-1) ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(string,',',i),',',-1) DESC) n 
   FROM my_table x
      , ints
  GROUP 
     BY id;

 +----+--------+-------+
 | id | string | n     |
 +----+--------+-------+
 |  1 | 1,2,3  | 3,2,1 |
 |  2 | 2,3,4  | 4,3,2 |
 |  3 | 3,4,5  | 5,4,3 |
 |  4 | 1,3,5  | 5,3,1 |
 |  5 | 2,4,6  | 6,4,2 |
 +----+--------+-------+

VMai · Accepted Answer · 2014-08-09T11:54:31.937

Assuming TableA looks as that

CREATE TABLE A (
    id INT,
    columnA INT
);

following approach would give you the desired result (as I understand it) for up to 100 separated values:

SELECT * FROM A
INNER JOIN (
    SELECT MAX(t.value) as max_value
    FROM (
        SELECT
            id, 
            SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', n.n), ',', -1) value
        FROM B CROSS JOIN (
            -- build for up to 100 separated values
            SELECT 
                a.N + b.N * 10 + 1 AS n
            FROM
                (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
               ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) n
        WHERE n <= (1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', '')))
        AND B.id = 21
    ) t
) t1
ON A.columnA = t1.max_value
;

Demo

Explanation

The most inner SELECT creates a temporary table with the values of 1 to 100:

    SELECT 
        a.N + b.N * 10 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n

This method is a usual approach to generate such lists and is fast.

The nested SUBSTRING call is responsible to get our values, test it with

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 1), ',', -1) FROM B
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 2), ',', -1) FROM B

to get a feeling for what it does. We limit our search with

1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', ''))

because we have one comma less than values and we need the last value too. So the statement

SELECT
    id, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', n.n), ',', -1) value
FROM B CROSS JOIN (
    SELECT 
        a.N + b.N * 10 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n <= (1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', '')))
AND B.id = 21

will return the list of the values from the column1 corresponding to id = 21.

The rest is a simple join on the maximum value of this list to the corresponding column of TableA.

FIND_IN_SET with comma seperated values for sorting condition

2 Answers2

Explanation

Linked