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

Question

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.

[EDIT]

When I replace u.UserID in the WHERE clause with an actual User ID, SQL Server 2000 doesn't complain. It seems that SQL 2000 can't handle linking the inner reference (u.UserID) to the outer table (User u).

Again, the error thrown is:

Msg 8624, Level 16, State 16, Line 24
Internal SQL Server error.

Answer 1

Since the problem was due to not being able to resolve the inner reference "uo.UserID" in the line "INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID", I replaced the line with a call to a function which accepts the uo.UserID value as a parameter.

This doesn't solve the problem elegantly, but it got the job done (although with a slight performance hit).