Select rows with maximum value where values in two columns are same

Question

I have a simple table like this

....................................
| hotelNo | roomType | totalBooking |
....................................
| 1       | single   |    2         |
| 1       | family   |    4         |
| 2       | single   |    3         |
| 2       | family   |    2         |
| 3       | single   |    1         |
.....................................

Now I want to get the most commonly booked roomType for each hotels, i.e the following result

......................
| hotelNo | roomType |
......................
|  1      | family   |
|  2      | single   |
|  3      | single   |
......................

P.S I use sub-query to get the first table

Answer 1

If you want the maximum, you can use window functions:

select hotelNo, roomType
from (select t.*, row_number() over (partition by hotelNo order by totalBooking desc) as seqnum
      from table t
     ) t
where seqnum = 1;

Answer 2

Sample table

SELECT * INTO #TEMP
FROM
(
    SELECT 1 HOTELNO ,'SINGLE' ROOMTYPE ,2 TOTALBOOKING
    UNION ALL
    SELECT 1,'FAMILY',4
    UNION ALL
    SELECT 2,'SINGLE',3
    UNION ALL
    SELECT 2,'FAMILY',2
    UNION ALL
    SELECT 3,'SINGLE',1
)TAB

Result query

;WITH CTE1 AS
(
   SELECT HOTELNO,ROOMTYPE,TOTALBOOKING,
   MAX(TOTALBOOKING) OVER (PARTITION BY HOTELNO) MAXX
   FROM #TEMP
)
SELECT DISTINCT HOTELNO,ROOMTYPE,MAXX
FROM CTE1 
WHERE TOTALBOOKING=MAXX