Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

Posted by Jay on Stack Overflow See other posts from Stack Overflow or by Jay
Published on 2010-05-13T00:10:28Z Indexed on 2010/05/13 0:14 UTC
Read the original article Hit count: 816

Filed under:
|

Here is the query:

INSERT INTO @TempTable
SELECT UserID,

Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder DESC), Address2 = (SELECT TOP 1 [Address] FROM (SELECT TOP 2 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder DESC) FROM User u

In this scenario, users have multiple address definitions, with an integer field specifying the preferred order. "Address2" (the second preferred address) attempts to take the top two preferred addresses, order them descending, then take the top one from the result. You might say, just use a subquery which does a SELECT for the record with "2" in the Order field, but the Order values are not contiguous.

How can this be rewritten to conform to SQL 2000's limitations?

Very much appreciated.

© Stack Overflow or respective owner

Related posts about nested

Related posts about select