asked on

SQL Query - Max Value

I need help with a query. I have a nested query that pull from 2 tables with a union all. I want to combine the 2 record it pull, but the greater value of the 2 into 1 record. Or more like if it's NULL then use the other value from the other record.

See my query and example output.

SELECT 	X.ID, X.ORDER_ID, X.DATE, X.STATUS, X.ORDER_POLICY
FROM	(
	SELECT 	ITEM_ID AS ID, ORDER_ID, TRANSACTION_DATE AS DATE, NULL AS STATUS, AS NULL ORDER_POLICY
	FROM	SALES_ORDER
	WHERE	
	UNION ALL
	SELECT	ID, NULL AS ORDER_ID, NULL AS DATE, NULL AS STATUS, ORDER_POLICY
	FROM	ITEMS
	) X
WHERE	X.ID = '129892' AND ISNULL(NULLIF(X.ORDER_ID, ''), '' = 'SO12342'

Open in new window

output-sample.jpg

Sean Stuber

SELECT x.id, MAX(x.order_id), MAX(x.date), MAX(x.status), MAX(x.order_policy)
FROM (SELECT item_id AS id,
order_id,
transaction_date AS date,
NULL AS status,
NULL AS order_policy
FROM sales_order
where
UNION ALL
SELECT id, NULL AS order_id, NULL AS date, NULL AS status, order_policy FROM items) x
GROUP BY id
HAVING x.id = '129892' AND x.order_id = 'SO12342'

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial