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

sql, sql-server-2008

Solution

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;

Problem

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

Original source