I want to use different ORDER BY in a UNION

I would like to pull the two most popular items from a database then
pull two random ones, and produce one resultset to work with.

My two queries are ;

SELECT * FROM product INNER JOIN stock ON product.id_product =
stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
product.viewed + product.weight DESC LIMIT 2 ;

and

SELECT * FROM product INNER JOIN stock ON product.id_product =
stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
rand() LIMIT 2;

they each work individually, but i cannot UNIONise them because I think
ORDER BY works on a single result set. How can I avoid this ?

Or is it possible to run a query, then append another result resource
(before fetching the array) to the first in php ?

thanks

Re: I want to use different ORDER BY in a UNION

> SELECT * FROM product INNER JOIN stock ON product.id_product =
> stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
> product.viewed + product.weight DESC LIMIT 2 ;

> SELECT * FROM product INNER JOIN stock ON product.id_product =
> stock.id_product WHERE price < $maxspend AND id_currency = 1 ORDER BY
> rand() LIMIT 2;

> they each work individually, but i cannot UNIONise them because I think
> ORDER BY works on a single result set. How can I avoid this ?

> Or is it possible to run a query, then append another result resource
> (before fetching the array) to the first in php ?

Use a temporary table.

First create a temp table using CREATE TEMPORARY TABLE
.

Use INSERT INTO ... SELECT to populate it from each of your queries
.

SELECT all the rows from the temporary table, ordering them however you
wish.

DROP the temporary table


---
Steve