MySQL - UNION over SELECT result

Question

In my table is plenty of rows, I prepare test_result_table:

SELECT * FROM 
(
  SELECT ID, DATA
  FROM table
  WHERE DATA = "A" OR DATA = "B" OR DATA = "C"
) AS test_result_table

How I can do this:

SELECT * FROM
    (
        SELECT 1 AS RESULT, ID 
        FROM test_result_table
        WHERE DATA = "A"
      UNION  
        SELECT 2 AS RESULT, ID 
        FROM test_result_table
        WHERE DATA = "B"
      UNION
        SELECT 3 AS RESULT, ID 
        FROM test_result_table
        WHERE DATA = "C"
    ) AS result_id
ORDER BY RESULT

I know to do with temp table, is any better (faster) solution?

I expect (x = some ID):

RESULT |ID
1      |x
1      |x 
2      |x
2      |x
3      |x

forpas · Accepted Answer · 2019-10-29 18:45:56Z

1

You can get the same results by using the function FIND_IN_SET() which will return the value of the column RESULT:

SELECT t.*
FROM (
  SELECT 
    FIND_IN_SET(DATA, 'A,B,C') AS RESULT, 
    ID
  FROM tablename
) t
WHERE t.RESULT > 0
ORDER BY t.RESULT

See a simplified demo.
I can't tell about it's efficiency, but it surely is simpler.

edited Oct 29, 2019 at 18:45

answered Oct 29, 2019 at 18:39

forpas

165k10 gold badges51 silver badges85 bronze badges

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

1 Comment

Mirek Šalamon Over a year ago

Thanks for idea... it is 2time faster than my UNION over my 2mio rows

Collectives™ on Stack Overflow

MySQL - UNION over SELECT result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related