Mysql: UNION after ORDER BY and LIMIT

My goal is to execute two different queries and then combine them.
My code is:

SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0,1 
UNION   
SELECT * FROM some tables WHERE ...

I get the following error:

#1221 - Incorrect usage of UNION and ORDER BY

It is important that ORDER BY is only for the first query. How can I perform this task?

 Answers

You can use parenthesis to allow the use of ORDER/LIMIT on individual queries:

(SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0, 1)
UNION   
(SELECT * FROM some tables WHERE ...)
ORDER BY 1   /* optional -- applies to the UNIONed result */
LIMIT 0, 100 /* optional -- applies to the UNIONed result */

---

just put everything in round brackets:

(SELECT * FROM table1 ORDER BY datetime  )
UNION   
(SELECT * FROM table2 ORDER BY datetime DESC)

MySQL JOIN - & Get all the data from the right table even if there is no match advertisements

I have two tables, one with data (A) and other with categories (B) which is in one of those categories or uncategorized. I need to make simple stats, so I am using JOIN to get all data from table A and join it with table B.

What I need is, that even if there is no match in A, I want to show all categories from B on graph (with count = 0, but I need them to be shown).

So if there are no items with that category id, it does not show that category at all, but I need to show it, even if it has 0 items.

I tried LEFT JOIN, RIGHT JOIN, both of them with UNION and nothing worked.

Table A

id | some | irrelevant | data | category
-----------------------------------------
1  |      |            |      | 0
2  |      |            |      | 0
3  |      |            |      | 1
4  |      |            |      | 2
5  |      |            |      | 3
6  |      |            |      | 3
. . . . . . . . . . .

Table B

id | title  | color
---------------------
1  | Cat 1  | #FFFFFF
2  | Cat 2  | #FF00FF
3  | Cat 3  | #FF0000
4  | Cat 4  | #00FF00
5  | Cat 5  | #00FFFF
6  | Cat 6  | #000000
7  | Cat 7  | #FFFF00

With those data I want to get something like:

Array ( [category] => 0 [count] => 3 [title] => [color] => )
Array ( [category] => 1 [count] => 1 [title] => Cat 1 [color] => #FFFFFF )
Array ( [category] => 2 [count] => 1 [title] => Cat 2 [color] => #FF00FF )
Array ( [category] => 3 [count] => 2 [title] => Cat 3 [color] => #FF0000 )
Array ( [category] => 4 [count] => 0 [title] => Cat 4 [color] => #00FF00 )
Array ( [category] => 5 [count] => 0 [title] => Cat 5 [color] => #00FFFF )
Array ( [category] => 6 [count] => 0 [title] => Cat 6 [color] => #000000 )
Array ( [category] => 7 [count] => 0 [title] => Cat 7 [color] => #FFFF00 )

But thats what I actually get:

Array ( [category] => 0 [count] => 3 [title] => [color] => )
Array ( [category] => 1 [count] => 1 [title] => Cat 1 [color] => #FFFFFF )
Array ( [category] => 2 [count] => 1 [title] => Cat 2 [color] => #FF00FF )
Array ( [category] => 3 [count] => 2 [title] => Cat 3 [color] => #FF0000 )

This is the call:

"SELECT category, COUNT(*) AS count, title, color FROM A LEFT JOIN B ON B.id=A.category GROUP BY category"

PS.: Once more, I trued RIGHT join too and UNION of RIGHT and LEFT join. Also, the names of tables are really long, cant really use them in call as A.category etc.

Is there some way to do that without using two SQL calls and two loops? I really don't want to do that.

Thanks.

---

And what about this :

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE TableA
    (`id` int, `category` int)
;

INSERT INTO TableA
    (`id`, `category`)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 2),
    (5, 3),
    (6, 3)
;

CREATE TABLE TableB
    (`id` int, `title` varchar(5), `color` varchar(7))
;

INSERT INTO TableB
    (`id`, `title`, `color`)
VALUES
    (1, 'Cat 1', '#FFFFFF'),
    (2, 'Cat 2', '#FF00FF'),
    (3, 'Cat 3', '#FF0000'),
    (4, 'Cat 4', '#00FF00'),
    (5, 'Cat 5', '#00FFFF'),
    (6, 'Cat 6', '#000000'),
    (7, 'Cat 7', '#FFFF00')
;

Query 1:

SELECT TableB.id as category, count(distinct TableA.id) as count,title,color
FROM
(SELECT * FROM TableB
UNION
SELECT 0 as id, '' as title, '' as color) AS TableB
LEFT OUTER JOIN TableA on TableB.id = TableA.category
GROUP BY TableB.id, title, color
ORDER BY TableB.id

Results:

| CATEGORY | COUNT | TITLE |   COLOR |
|----------|-------|-------|---------|
|        0 |     2 |       |         |
|        1 |     1 | Cat 1 | #FFFFFF |
|        2 |     1 | Cat 2 | #FF00FF |
|        3 |     2 | Cat 3 | #FF0000 |
|        4 |     0 | Cat 4 | #00FF00 |
|        5 |     0 | Cat 5 | #00FFFF |
|        6 |     0 | Cat 6 | #000000 |
|        7 |     0 | Cat 7 | #FFFF00 |

Also, instead of using UNION SELECT 0 as id, '' as title, '' as color, you can add a category with id 0 in your TableB

How to optimize the MySQL query with many unions

I have this query which I need help with. So there is a table called insertjobticketwith column called DEL which is a long character field which can have multiple dates in it. I need to create an output table which contains one row for each time there is a date in the DEL field for a certain range of dates.

The reason I can't just do a more simple select ... where ... DEL like "%my_date%" is that the DEL column can contain multiple dates, and if so, I need to return multiple rows to the output set, one row for each date that appears in the DEL column.

The solution I came up with that works, but is very slow looks like this:

create temporary table jobtrack.ship_helpert3 as
select * from
(
    (
    select
        date_format(now() - interval 3 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() - interval 3 day, '%m/%d/%Y'),'%')
    ) union (
    select
        date_format(now() + interval 2 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() + interval 2 day, '%m/%d/%Y'),'%')
    ) union (
    select
        date_format(now() + interval 1 day, '%m/%d/%Y') as `Ship_Date`,
        more_columns
    from
        jobticket.insertjobticket
    where
        DEL like concat('%',date_format(now() + interval 1 day, '%m/%d/%Y'),'%')
    ) union ...
) t;

Each select query checks if there are any rows with a certain date string (date_format(now() + interval @x day, '%m/%d/%Y')) in the DEL field. The query is built programmatically and can get very long, as I would like to be able to make the query check for many many dates.

The insertjobticket table contains 40K rows and is growing, so the query above takes way too long to complete. I understand why it takes so long, because every unioneffectively has to make its own sub-query that scans the whole table again and again for each date. I just don't know how to make this work more efficiently.

Does anyone know how to speed up this query?

Thanks for the help and let me know if we need more clarification.

---

As already stretched in the comments, the only correct solution would be to normalize your data, that means to create a new table with one delivery date and the primary key of insertjobticket per row, and let the application use this table directly instead of the column del, or at least indirectly by a trigger that updates this table everytime the column DEL is updated.

Since you cannot do that, the following workaround should improve your query:

select
  del_dates.Ship_Date,
  othercolumns
from insertjobticket
join (
    select concat(date_format(now() + interval 2 day, '%m/%d/%Y'))
           collate utf8_general_ci as Ship_Date
    union select concat(date_format(now() + interval 1 day, '%m/%d/%Y'))
    union select concat(date_format(now() + interval -15 day, '%m/%d/%Y'))
    ...
) del_dates
on insertjobticket.del like concat('%', del_dates.Ship_Date, '%');

(Change the collation to the one you use in your table or leave it away to see which one, if any, you need).

This will basically do the required normalization step (for the requested dates) every time you execute the query, and will not be able to use indexes. Just make sure your explain output shows using join buffer for the derived table, not for insertjobticket, otherwise replace join with a straight_join.

For 40k rows, this might not be a big a problem, and there is no other way around it anyway, except real normalization. Keep in mind that your query will slow down linearly with the amount of rows (400k rows will take about 10 times the time as 40k), an effect indexes would prevent. So if it is too slow now (or sometimes in the future), you eventually have to normalize (or, as a workaround to the problems created by this workaround, add a column to mark old entries and exclude them in your join condition).

Btw, since you generate your code programmatically, it shouldn't be a problem to create the list of dates, otherwise you can use another subquery to generate a list of general dates and just select the ones in a specific range.