PHP SQL - I want to use different ORDER BY in a UNION

Author

I want to use different ORDER BY in a UNION

charliefortune

2005-12-02, 9:05 pm

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

Steve

2005-12-02, 9:05 pm

> 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
<http://dev.mysql.com/doc/refman/4.1...eate-table.html>.

Use INSERT INTO ... SELECT to populate it from each of your queries
<http://dev.mysql.com/doc/refman/4.1...ert-select.html>.

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

DROP the temporary table
<http://dev.mysql.com/doc/refman/4.1/en/drop-table.html>

---
Steve

Sponsored Links