How do I Concatenate entire result sets in MySQL?

I'm trying out the following query:

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

That's three queries stuck together, kindasorta. However, the result set that comes back reflects results from query #3 BEFORE results from query #1 (undesired).

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).

Cheers,

edit: many thanks to all! the solution turned out to be a mix of all answers. worked beautifully!.

/mp

This question and answers originated from www.stackoverflow.com
Question by (8/6/2008 6:47:24 PM)

Answer

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
Find More Answers
Related Topics  mysql  query  union
Related Questions

  • When doing a UNION in mysql how can I do a where on the results

    Hi I am doing a union over several tables. It's a little long but works! (SELECT user_id,added_date,group_id,'joined',0,0,'' FROM group_members WHERE status = 1) UNION (SELECT user_id,added_date,…

  • Problem in Union Join For MySQL Query

    i managed to select from a table that saves my latest posts but i need to have double condition in selection here is my code : $sql_query = "SELECT b.*,u.username AS MY_Sender FROM TABLE_us…

  • Why are UNION queries so slow in MySQL?

    When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run. Also, a UNION ALL takes longer than a UNION DISTINCT. I would…

  • Selecting two rows from a single table row in mySQL

    I have a table A (Id1, Id2, someValue) What I want to achieve in mySQL: My SELECT query should return two rows: Id1, someValue Id2, someValue (based on a certain condition - for example…

  • MySQL Union for two tables, then merge in a third table where matched?

    I have three MySQL tables that I'm trying to query into a single result. As close as I've gotten this, I think it's possible, but I've hit a wall in getting the last part working. Basically I hav…

  • Union as sub query MySQL

    I'm wanting to optimize a query using a union as a sub query. Im not really sure how to construct the query though. I'm using MYSQL 5 Here is the original query: SELECT Parts.id FROM Parts_C…

  • Removing duplication for this MYSQL union query

    I have a query with duplicate expressions in it. Could it be written more compact? select id from `vacature_saved_searches` where `saved_search_interval` = 1 and DAYOFWEEK(CURDATE()) …

  • Creating a grid of data with php/mysql - union queries?

    I'm attempting to create a grid of availability from a number of tables to present information as follows: ------------------------------------------------------ venue | 22/11 | 23/11 | 24/11 |…

  • MySQL: Add an identifying field to UNION queries

    If I have a MySQL query that contains a union, is there a way to add a custom field to each record in my result set that identifies which query that record is a result of? So if my result set was…

  • How to Sum Columns From Two Result Sets in MySQL

    I have the following table: ratings: ID | post_id | rating_type The rating_type field is either 'thumb-up' or 'thumb-down'. I want to get a result set telling me what the highest rated pos…