Geek Answers Handbook

SQL Server ORDER BY / WHERE with Nested Selection

I am trying to get SQL Server to order by column from a nested select. I know that this is not the best way to do this, but it needs to be done.

I have two tables, Reservation and Reservation. BookingItems has StartDate and EndDate fields, and there may be several reservations when booking. I need to find the earliest start date and last end date using BookItems, then filter and sort by these values.

I tried with a nested select, but when I try to use one of the selected columns in WHERE or ORDER BY, I get "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 ordering SQL? I am using SQL Server 2008.

+5
2

. , ?

, , :

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
+7

RE:

WHERE ORDER BY, " ".

ORDER BY WHERE. , ?

() . 5, ORDER BY, WHERE

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

- , . , , !

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'

http://dbaspot.com/forums/sqlserver-programming/392124-when-can-we-use-column-alias-where-group-having-order.html#8

+4

More articles:


All Articles