CODEWITHSUNDEEP
sqlsql-server
SaSquadge
SaSquadge

Reputation: 219

SQL Server MAX of Multiple Names

I'm trying to create a query to get all the MAX counts of a room that have a reservation, my query returns everything correctly but when I try to find MAX it will only return 1 result instead of 3 that should be showing up it will only return the max count.

Current Results: Shows only the MAX of 1 hotel

HotelName                      RoomType             Room Count
------------------------------ -------------------- -----------
MGM Grand                      Single               6

Results of the query before the HAVING COUNT:

HotelName                      RoomType             Room Count
------------------------------ -------------------- -----------
The Mirage                     Double               3
The Palazzo                    Double               1
MGM Grand                      Double Deluxe        4
MGM Grand                      Family               2
The Mirage                     Family               4
MGM Grand                      Single               6
The Palazzo                    Suite                1

Expected Results: Note: on The Palazzo since is has two that are the same count having it show both or one or another would work

HotelName                      RoomType             Room Count
------------------------------ -------------------- -----------
MGM Grand                      Single               6
The Mirage                     Family               4
The Palazzo                    Suite                1

Current Script:

SELECT h.HotelName, hr.RoomType, COUNT(*) AS 'Room Count'
FROM RESERVATIONDETAIL AS rd
JOIN ROOM r ON r.RoomID = rd.RoomID
JOIN HOTELROOMTYPE hr ON hr.HotelRoomTypeID = r.HotelRoomTypeID 
JOIN HOTEL h ON h.HotelID = hr.HotelID
WHERE rd.CheckinDate >= '140901' AND rd.CheckinDate <= '141031'
GROUP BY h.HotelName, hr.RoomType
HAVING COUNT(*) = 
(SELECT MAX(RoomCount)
FROM 
(SELECT h.HotelName, COUNT(*) AS RoomCount
FROM RESERVATIONDETAIL AS rd
JOIN ROOM r ON r.RoomID = rd.RoomID
JOIN HOTELROOMTYPE hr ON hr.HotelRoomTypeID = r.HotelRoomTypeID 
JOIN HOTEL h ON h.HotelID = hr.HotelID
WHERE rd.CheckinDate >= '140901' AND rd.CheckinDate <= '141031'
GROUP BY h.HotelName, hr.RoomType)x)

Upvotes: 1

Views: 60

Answers (1)

erdomke
erdomke

Reputation: 5245

I think that the row_number function should get you what you want. There might be a way to do this with one less subquery, but I believe the general idea would be:

select d.HotelName, d.RoomType, d.[Room Count]
from (
  select d.*, row_number() over (partition by HotelName order by [Room Count] desc) rn
  from (
    SELECT h.HotelName, hr.RoomType, COUNT(*) AS 'Room Count'
    FROM RESERVATIONDETAIL AS rd
    JOIN ROOM r ON r.RoomID = rd.RoomID
    JOIN HOTELROOMTYPE hr ON hr.HotelRoomTypeID = r.HotelRoomTypeID 
    JOIN HOTEL h ON h.HotelID = hr.HotelID
    WHERE rd.CheckinDate >= '140901' AND rd.CheckinDate <= '141031'
    GROUP BY h.HotelName, hr.RoomType
  ) d
) d
where d.rn = 1

Upvotes: 2

Related Questions