Ian Littlefair wrote:
>
> If the order_number exists in query1 and query2 it should only appear in the
> output from the highest query i.e query2 and not also in query1. Similarly if
> the order_number appears in the output from query1 and also query6 I only
> want to show it in the output from the highest query i.e query6. Hope that
> makes sense.
So when is a result of query 5 a duplicate of one of the other
queries? It doesn't even have the same fields.
For the other queries, use:
SELECT
ORDER_NUMBER,
sub_zone,
MAX(q) AS q
FROM
(
SELECT select ORDER_NUMBER, sub_zone, 1 AS q
rest_of_query_1
UNION ALL
SELECT select ORDER_NUMBER, sub_zone, 2 AS q
rest_of_query_2
UNION ALL
SELECT select ORDER_NUMBER, sub_zone, 3 AS q
rest_of_query_3
UNION ALL
SELECT select ORDER_NUMBER, sub_zone, 4 AS q
rest_of_query_4
UNION ALL
SELECT select ORDER_NUMBER, sub_zone, 6 AS q
rest_of_query_6
) allresults
GROUP BY
ORDER_NUMBER,
sub_zone
ORDER BY
MAX(q) DESC,
sub_zone
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185446
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
