<p>I'm trying out the following query:</p>

<pre><code>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%'
GROUP BY B ORDER BY B ASC LIMIT 5
</code></pre>

<p>That's three queries stuck together, kinda sorta. However, the result set that comes back reflects results from query #3 before the results from query #1 which is undesired.</p>

<p>Is there any way to prioritize these so that results come as all for query #1, then all for query #2 then all for query #3? I don't want to do this in PHP just yet (not to mention having to control for results that showed up in the first query not to show in the second and so forth).</p>

[{'answer_id': 3852, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 2, 'selected': False, 'text': "<p>Can you do it as a subselect, something like</p>\n\n<pre><code>SELECT * FROM (\n    SELECT A,B,C FROM table WHERE field LIKE 'query%'\n    UNION\n    SELECT A,B,C FROM table WHERE field LIKE '%query'\n    UNION\n    SELECT A,B,C FROM table WHERE field LIKE '%query%'\n) ORDER BY B ASC LIMIT 5\n</code></pre>\n"}, {'answer_id': 3853, 'author': 'Greg Hurlman', 'author_id': 35, 'author_profile': 'https://Stackoverflow.com/users/35', 'pm_score': 0, 'selected': False, 'text': "<p>If there isn't a sort that makes sense to order them you desire, don't union the results together - just return 3 separate recordsets, and deal with them accordingly in your data tier.</p>\n"}, {'answer_id': 3854, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 3, 'selected': False, 'text': "<p>Add an additional column with hard-coded values that you will use to sort the overall resultset, like so:</p>\n\n<pre><code>SELECT A,B,C,1 as [order] FROM table WHERE field LIKE 'query%'\nUNION\nSELECT A,B,C,2 as [order] FROM table WHERE field LIKE '%query'\nUNION\nSELECT A,B,C,3 as [order] FROM table WHERE field LIKE '%query%'\nGROUP BY B ORDER BY [order] ASC, B ASC LIMIT 5\n</code></pre>\n"}, {'answer_id': 3860, 'author': 'Mario Marinato', 'author_id': 431, 'author_profile': 'https://Stackoverflow.com/users/431', 'pm_score': 5, 'selected': True, 'text': '<p>Maybe you should try including a fourth column, stating the table it came from, and then order and group by it:</p>\n\n<pre><code>SELECT A,B,C, "query 1" as origin FROM table WHERE field LIKE \'query%\'\nUNION\nSELECT A,B,C, "query 2" as origin FROM table WHERE field LIKE \'%query\'\nUNION\nSELECT A,B,C, "query 3" as origin FROM table WHERE field LIKE \'%query%\'\nGROUP BY origin, B ORDER BY origin, B ASC LIMIT 5\n</code></pre>\n'}, {'answer_id': 3876, 'author': 'mauriciopastrana', 'author_id': 547, 'author_profile': 'https://Stackoverflow.com/users/547', 'pm_score': 0, 'selected': False, 'text': '<p>I eventually (looking at all suggestions) came to this solution, its a bit of a compromise between what I need and time.</p>\n\n<pre><code>SELECT * FROM \n  (SELECT A, B, C, "1" FROM table WHERE B LIKE \'query%\' LIMIT 3\n   UNION\n   SELECT A, B, C, "2" FROM table WHERE B LIKE \'%query%\' LIMIT 5)\nAS RS\nGROUP BY B\nORDER BY 1 DESC\n</code></pre>\n\n<p>it delivers 5 results total, sorts from the fourth "column" and gives me what I need; a natural result set (its coming over AJAX), and a wildcard result set following right after.</p>\n\n<p>:)</p>\n\n<p>/mp</p>\n'}, {'answer_id': 3902, 'author': 'svrist', 'author_id': 86, 'author_profile': 'https://Stackoverflow.com/users/86', 'pm_score': 1, 'selected': False, 'text': "<p>SELECT distinct a,b,c  FROM (\n        SELECT A,B,C,1 as o FROM table WHERE field LIKE 'query%'\n        UNION\n        SELECT A,B,C,2 as o FROM table WHERE field LIKE '%query'\n        UNION\n        SELECT A,B,C,3 as o FROM table WHERE field LIKE '%query%'\n    )\n    ORDER BY o ASC LIMIT 5</p>\n\n<p>Would be my way of doing it. I dont know how that scales.</p>\n\n<p>I don't understand the</p>\n\n<pre><code>GROUP BY B ORDER BY B ASC LIMIT 5\n</code></pre>\n\n<p>Does it apply only to the last SELECT in the union? </p>\n\n<p>Does mysql actually allow you to group by a column and still not do aggregates on the other columns?</p>\n\n<p>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 :)</p>\n"}, {'answer_id': 84974, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>There are two varients of UNION. </p>\n\n<pre><code>'UNION' and 'UNION ALL'\n</code></pre>\n\n<p>In most cases what you really want to say is UNION ALL as it does not do duplicate elimination (Think <code>SELECT DISTINCT</code>) between sets which can result in quite a bit of savings in terms of execution time.</p>\n\n<p>Others have suggested multiple result sets which is a workable solution however I would caution against this in time sensitive applications or applications connected over WANs as doing so can result in significantly more round trips on the wire between server and client.</p>\n"}, {'answer_id': 56214651, 'author': 'harsha reddy', 'author_id': 11315515, 'author_profile': 'https://Stackoverflow.com/users/11315515', 'pm_score': 0, 'selected': False, 'text': "<p>I don't understand why the need of union for taking the data from single table</p>\n\n<pre><code>SELECT A, B, C \nFROM table \nWHERE field LIKE 'query%' \n   OR field LIKE '%query' \n   OR field LIKE '%query%'\nGROUP BY B \nORDER BY B ASC LIMIT 5\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/3839', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/547/']