I’m encountering an issue with a MySQL query that uses UNION to combine several SELECT statements, and I’m struggling with how the results are ordered.
This is the query I’m currently using:
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
The challenge is that the results from the third query show up before the first, which is not what I need. I would like the results from the first query to be listed first, followed by the second, and then the third.
Is there a method to enforce this order in MySQL, rather than handling it in the application code? I also want to prevent any duplicates from showing up in the combined results.
I’ve hit this exact problem building search features. Your SQL issue is that GROUP BY and ORDER BY apply to the whole UNION result, not keeping your priority order.
Here’s the fix - add a priority column:
SELECT A, B, C, 1 as priority FROM table WHERE field LIKE 'query%'
UNION
SELECT A, B, C, 2 as priority FROM table WHERE field LIKE '%query'
UNION
SELECT A, B, C, 3 as priority FROM table WHERE field LIKE '%query%'
ORDER BY priority ASC, B ASC
LIMIT 5
Exact matches first, then suffix matches, then contains matches.
That said, I don’t handle complex search logic in my apps anymore. I automate the whole pipeline with Latenode now.
Built a workflow that handles query execution, result prioritization, and duplicate removal automatically. Connects to MySQL, runs multiple prioritized queries, merges results properly, and caches for performance.
Best part? I can change search logic without touching code. Just update the workflow.
Those SQL solutions work, but you’ll hate maintaining them when search requirements change. I’ve been down this rabbit hole too many times.
The real problem isn’t just ordering - it’s hardcoding search logic that needs constant updates. What about fuzzy matching? Relevance scoring? Different priority rules?
I ditched complex UNION queries and built this as an automated workflow. Latenode connects to your MySQL database, runs each query in sequence, and collects results with proper priority ordering.
The workflow handles duplicate removal automatically and lets you adjust search patterns without touching SQL. You can add result caching, logging, and notifications when searches fail.
Set it up once and forget it. Way cleaner than nested subqueries and CASE statements everywhere.
Been wrestling with the same thing lately. CASE statements work but get messy when conditions overlap. I’ve had better luck with UNION ALL and explicit priority, then handling deduplication manually:
SELECT A, B, C FROM (
SELECT A, B, C, 1 as sort_order FROM table WHERE field LIKE 'query%'
UNION ALL
SELECT A, B, C, 2 as sort_order FROM table WHERE field LIKE '%query' AND field NOT LIKE 'query%'
UNION ALL
SELECT A, B, C, 3 as sort_order FROM table WHERE field LIKE '%query%' AND field NOT LIKE '%query' AND field NOT LIKE 'query%'
) combined
GROUP BY A, B, C
HAVING sort_order = MIN(sort_order)
ORDER BY sort_order, B
LIMIT 5
UNION ALL beats regular UNION since it skips the automatic duplicate removal. The HAVING clause makes sure each unique record only keeps its highest priority. Those NOT LIKE conditions stop records from showing up in multiple result sets, which would screw up your ordering.
wrap each select in a subquery and add order fields. this worked for me:
(SELECT A, B, C FROM table WHERE field LIKE 'query%' ORDER BY B LIMIT 10)
UNION
(SELECT A, B, C FROM table WHERE field LIKE '%query' ORDER BY B LIMIT 10)
UNION
(SELECT A, B, C FROM table WHERE field LIKE '%query%' ORDER BY B LIMIT 10)
LIMIT 5
Your priority column approach is on the right track, but there’s an issue with mixing GROUP BY and UNION. GROUP BY runs on the entire result set after the union finishes, which creates weird behavior. I’ve dealt with this before when building ranking systems - it’s better to handle duplicates upfront: sql SELECT DISTINCT A, B, C, CASE WHEN field LIKE 'query%' THEN 1 WHEN field LIKE '%query' AND field NOT LIKE 'query%' THEN 2 WHEN field LIKE '%query%' AND field NOT LIKE '%query' THEN 3 END as search_priority FROM table WHERE field LIKE 'query%' OR field LIKE '%query' OR field LIKE '%query%' ORDER BY search_priority, B ASC LIMIT 5 This ditches the UNION completely and uses DISTINCT for deduplication while keeping your priority order. The NOT LIKE conditions prevent overlap - each row only gets the highest priority match.