guy i was given a qeustion for my exam that
What is the most commonly booked room type for each hotel in London?

to this i replied

SELECT MAX(R.roomno)AS most room selected
      GROUP BY roomtype
      WHERE H.hotelno=B.hotelno AND city='london'

my problem is that i dont know if the logic is correct or not and mysql server is not working i would be obliged if some one can tell me that my logic is true thank you all

5 Years
Discussion Span
Last Post by adam_k

the main reason why i wrote it like this was simple the most no of room repeatedly placed in booking table (where roomno is not a primary key )be an easy winner but i dont know if its possible


Your query will return the maximum room number. By maximum I mean the bigger number, not the most frequently booked.

For that you will need count() function grouped by room number. Also it requires to have the room number in the select (in order to see which one it is) and to order by count desc.
If you just want the room number and not the number of times it has been booked you need to create a query with the above as a sub-query (or at least this is the easiest way to do it).

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.