SQL Server ORDER BY/WHERE with nested select

Question

I'm trying to get SQL Server to order by a column from a nested select. I know this isn't the best way of doing this but it needs to be done.

I have two tables, Bookings and BookingItems. BookingItems contains StartDate and EndDate fields, and there can be multiple BookingItems on a Booking. I need to find the earliest startdate and latest end date from BookingItems, then filter and sort by these values.

I've tried with a nested select, but when I try to use one of the selected columns in a WHERE or ORDER BY, I get an "Invalid Column Name".

SELECT b.*, (SELECT COUNT(*) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS TotalRooms,
        (SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS StartDate, 
        (SELECT MAX(i.EndDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS EndDate 
FROM bookings b LEFT JOIN customers c ON b.CustomerID = c.CustomerID  WHERE StartDate >= '2010-01-01'

Am I missing something about SQL ordering? I'm using SQL Server 2008.

Answer 1

RE:

when I try to use one of the selected columns in a WHERE or ORDER BY, I get an "Invalid Column Name".

You will need to use different approaches for ORDER BY and WHERE. Can you post the exact code you were trying?

The below is the (general) order of evaluation. Column Aliases are only available from step 5 onwards so are usable in ORDER BY but not WHERE

(5) SELECT (6) DISTINCT
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(7) ORDER BY

In the case of your posted Query you would need to do it something like this to avoid the error. I have spent zero time looking at the semantics to see if there is a better way though!

SELECT b.*, (SELECT COUNT(*) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS TotalRooms,
        (SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS StartDate, 
        (SELECT MAX(i.EndDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS EndDate 
FROM bookings b LEFT JOIN customers c ON b.CustomerID = c.CustomerID  
WHERE (SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) >= '2010-01-01'

This thread explains things in much more detail http://dbaspot.com/forums/sqlserver-programming/392124-when-can-we-use-column-alias-where-group-having-order.html#8

Answer 2

That shouldn't be a problem. Can you post the exact query that's failing?

Also, grouping your results will be easier and likely faster then running the nested queries:

SELECT 
  TotalRooms = COUNT(*)
, StartDate = MIN(i.StartDate)
, EndDate =  MAX(i.EndDate)
FROM bookings b 
LEFT JOIN bookingitems bi 
  ON b.BookingID = bi.BookingID
GROUP BY b.BookingID
WHERE MIN(i.StartDate) >= '2010-01-01'
ORDER BY StartDate, EndDate