3 way top +1 ??

[MichaelWClark]

I have 3 tables which hold PO's which have uniquie IDs I want to get the last ID in the list then add one to it (i can add one in my code if needed). My query thus far is

SELECT TOP 1
(SELECT 0 AS [PURCHASE_ORDER_ID]
UNION SELECT TOP 1
[PURCHASE_ORDER_ID] FROM 
[PURCHASE_ORDER_OFFICE_EQUIPMENT] 
WHERE
[PURCHASE_ORDER_PROPERTY_NAME] = 'Aubry Hills'
UNION 
SELECT TOP 1 
[PURCHASE_ORDER_ID] 
FROM 
[PURCHASE_ORDER_BUSINESS_EQUIPMENT] 
WHERE 
[PURCHASE_ORDER_PROPERTY_NAME] = 'Aubry Hills' 
UNION 
SELECT TOP 1
[PURCHASE_ORDER_ID] FROM 
[PURCHASE_ORDER_SOFTWARE_EQUIPMENT]
WHERE
[PURCHASE_ORDER_PROPERTY_NAME] ='Aubry Hills')
AS [PURCHASE_ORDER_ID]

right now i have a PURCHASE_ORDER_ID of 0 and 1 in my [PURCHASE_ORDER_OFFICE_EQUIPMENT] ,....so when i get this query right it should return 1 rather than 0..... pls help sql gurus :)

[Ezzaral]

Well, having a master PO table would alleviate the need altogether, but aside from that, you could use max() instead of 'top'. Top is dependent upon the returned order of the rows, whereas max() is not. i.e.

select max(maxId)+1 from
(select max(Id) as maxId from a 
union 
select max(Id) as maxId from b
union
select max(Id) as maxId from c) as allIds

[MichaelWClark]

TYVM...and yes the design isnt optimal, but I am a programmer not a DB Designer :P I generally design so its easier for my code than for my sql ;)