UNION of two queries using a subquery?

Question

Is it possible to have something like:

SELECT MAX(id), somefield UNION ALL SELECT MIN(id), someotherfield FROM (SOME GIANT SQL JOIN)

Basically, I want to use the same join subquery in the two UNIONed queries.

Sorry: the syntax is basically: query1 union query2 (so: both subqueries each need a FROM ... ) And: union is almost always an indication of bad design. — wildplasser
– wildplasser, Commented Aug 15, 2014 at 22:47
Well, the uses of UNION you have in your mind might be bad design. It's almost always incorrect to assume bad design when the use-case is not known. And the answer below works. — mmtauqir
– mmtauqir, Commented Aug 15, 2014 at 23:47
You are correct: I don't know the use-case But How could I know, you did not show it... BTW: a syntactically incorrect snippet in your question wont help you. — wildplasser
– wildplasser, Commented Aug 15, 2014 at 23:52
Yes. Syntax wasn't the point of the snippet. I received the help I wanted. Thank you. — mmtauqir
– mmtauqir, Commented Aug 15, 2014 at 23:58
Syntax wasn't the point of the snippet. I am going to make sticker with that text on it. Thank you! — wildplasser
– wildplasser, Commented Aug 16, 2014 at 0:00

Brian DeMilia · Accepted Answer · 2014-08-15 22:29:43Z

You could use the with clause, in this manner:

with sub as (some giant sql join) select max(id), somefield from sub group by somefield union all select min(id), someotherfield from sub group by someotherfield

(as an alternative to writing out the same inline view twice)

Collectives™ on Stack Overflow

UNION of two queries using a subquery?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related