0

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 :)

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by MichaelWClark
0

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
0

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 ;)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.