Union Statement Help

[jedooley]

Can someone tell me if there is anything wrong with this statement. It pulls the right data in the first statement but always the wrong data in the second.
I want it to pull the last record in the table that meets the where clause?

The first statement pulls the last record (which is correct) while the 2nd statement is pulling the first right now?


SELECT top 1 (Inventory_ID), InvDate, [Inventory].[Opening]
FROM Inventory
WHERE [product_ID]=1 And [Location_ID]=1
order by Inventory_ID desc

UNION SELECT top 1 (Inventory_ID), InvDate, [Inventory].[Opening]
FROM Inventory
WHERE [product_ID]=1 And [Location_ID]=2
ORDER BY Inventory_ID DESC;

 

[WayneRyan]

je,

Syntactically, you look OK.

A good way to check it out is to remove the "TOP 1" predicate and check
your results.

Wayne

 

[jedooley]

The Statment gives me the correct bottom record for the first statement but gives the correct top record for the second statement when I need the bottom record.

 

[WayneRyan]

je,

Like I said, it looks OK. Syntactically and logically ... fine.

Can you post it?

Wayne

 

[jedooley]

Thanks for your help. I have attached the table and results

Its still not working quite right. Please help

 

[pdx_man]

It is not executing the last ORDER BY before it does the UNION:


SELECT top 1 Inventory.Inventory_ID, Inventory.InvDate
FROM Inventory
WHERE Inventory.product_ID=1 AND Inventory.Location_ID=1
ORDER BY Inventory.Inventory_ID DESC
UNION
(SELECT TOP 1 Inventory.Inventory_ID , Inventory.InvDate
FROM Inventory
WHERE Inventory.product_ID=1 AND Inventory.Location_ID=2
ORDER BY Inventory.Inventory_ID DESC)

 

[jedooley]

Thanks Alot Man, That worked great.