CODEWITHSUNDEEP
sqlsql-server-2008
asok Buzz
asok Buzz

Reputation: 1894

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

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

Upvotes: 4

Views: 559

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93704

Another way of doing is by using Aggregate Function Max

SELECT A.hotelNo,
       A.roomType,
       A.totalBooking
FROM   tablename A
       JOIN (SELECT Max (totalBooking) totalBooking,
                    hotelNo
             FROM   tablename
             group by hotelNo) B
         ON a.totalBooking = b.totalBooking
            AND a.hotelNo = b.hotelNo

SQL FIDDLE DEMO

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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;

Upvotes: 5

Related Questions