How do I Concatenate entire result sets in MySQL?

SELECT distinct a,b,c FROM ( SELECT A,B,C,1 as o FROM table WHERE field LIKE 'query%' UNION SELECT A,B,C,2 as o FROM table WHERE field LIKE '%query' UNION SELECT A,B,C,3 as o FROM table WHERE field LIKE '%query%' ) ORDER BY o ASC LIMIT 5

Would be my way of doing it. I dont know how that scales.

I don't understand the

GROUP BY B ORDER BY B ASC LIMIT 5

Does it apply only to the last SELECT in the union?

Does mysql actually allow you to group by a column and still not do aggregates on the other columns?

EDIT: aaahh. I see that mysql actually does. Its a special version of DISTINCT(b) or something. I wouldnt want to try to be an expert on that area :)

Can you do it as a subselect, something like

SELECT * FROM (
    SELECT A,B,C FROM table WHERE field LIKE 'query%'
    UNION
    SELECT A,B,C FROM table WHERE field LIKE '%query'
    UNION
    SELECT A,B,C FROM table WHERE field LIKE '%query%'
) ORDER BY B ASC LIMIT 5

Add an additional column with hard-coded values that you will use to sort the overall resultset, like so:

SELECT A,B,C,1 as [order] FROM table WHERE field LIKE 'query%'
UNION
SELECT A,B,C,2 as [order] FROM table WHERE field LIKE '%query'
UNION
SELECT A,B,C,3 as [order] FROM table WHERE field LIKE '%query%'
GROUP BY B ORDER BY [order] ASC, B ASC LIMIT 5

Maybe you should try including a fourth column, stating the table it came from, and then order and group by it:

SELECT A,B,C, "query 1" as origin FROM table WHERE field LIKE 'query%'
UNION
SELECT A,B,C, "query 2" as origin FROM table WHERE field LIKE '%query'
UNION
SELECT A,B,C, "query 3" as origin FROM table WHERE field LIKE '%query%'
GROUP BY origin, B ORDER BY origin, B ASC LIMIT 5