MYSQL, Subquery Reference in Union

Posted by christian on Stack Overflow See other posts from Stack Overflow or by christian
Published on 2012-04-15T16:59:55Z Indexed on 2012/04/15 17:28 UTC
Read the original article Hit count: 216

Filed under:
|

Is there any way to reference a subquery in a union?

I am trying to do something like the following, and would like to avoid a temporary table, but the subquery will be drawn from a much larger dataset so it makes sense to only do it once..

SELECT * FROM (SELECT * FROM ads WHERE state='FL' AND city='Maitland' AND page='home' ORDER BY RAND()) AS sq WHERE spot = 'full-banner' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'leaderboard' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'rectangle1' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'rectangle2' LIMIT 1
.... etc,,

It's a shame that DISTINCT can't be specified for a single column of a result set.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about subquery