CODEWITHSUNDEEP
phpmysqlsqldatabaseunion
PutraKg
PutraKg

Reputation: 2246

Combine two mysql query into one

What is the proper syntax to combine these two queries?

SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1

and

SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1

I tried:

SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1
UNION
SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1;

but I get "Incorrect usage of UNION and ORDER BY".

EDIT Additionally, I want the result to be returned in a single row. So that I can access the value in php eg 

$row['nextclick'] and $row['topclick']

From Simon's suggestion, I should not use UNION because I want to return a single row of data

Upvotes: 7

Views: 38986

Answers (3)

Simon Martin
Simon Martin

Reputation: 4231

You can't ORDER BY in your first SELECT and then UNION it.

Edit
You can however

apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

as in the MySQL UNION documentation 

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Which then makes your SQL 

(SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1)
UNION
(SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1);

Edit 2
To return in an array 

SELECT (SELECT clicks 
        FROM clicksTable 
        WHERE clicks > 199 
        ORDER BY clicks ASC 
        LIMIT 1) AS NextClick,
       (SELECT clicks 
        FROM clicksTable 
        ORDER BY clicks DESC 
        LIMIT 1) AS TopClick;

Upvotes: 18

Gordon Linoff
Gordon Linoff

Reputation: 1269533

First, do you want a union or a union all?

The problem is the order by in the first part. You can fix this, using subqueries:

(select * from (SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1))
UNION ALL
(select * from (SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1))

In a union expression, order by is only allowed at the end and it applies to the entire expression.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

SELECT clicks FROM
    (SELECT clicks FROM clicksTable WHERE clicks > 199 ORDER BY clicks ASC LIMIT 1) A
UNION
SELECT clicks FROM
    (SELECT clicks FROM clicksTable ORDER BY clicks DESC LIMIT 1) B;

Upvotes: 4

Related Questions