SQL query to group by data but with order by clause

Question

I have table booking in which I have data

GUEST_NO    HOTEL_NO    DATE_FROM   DATE_TO     ROOM_NO
1           1           2015-05-07  2015-05-08  103
1           1           2015-05-11  2015-05-12  104
1           1           2015-05-14  2015-05-15  103
1           1           2015-05-17  2015-05-20  101
2           2           2015-05-01  2015-05-02  204
2           2           2015-05-04  2015-05-05  203
2           2           2015-05-17  2015-05-22  202

What I want is to get the result as. 1 ) It should show output as Guest_no, Hotel_no, Room_no, and column with count as number of time previous three column combination repeated.

So OutPut should like

GUEST_NO    HOTEL_NO        ROOM_NO Count
1                1              103   2
1                1              104   1
1                1              101   1
2                2              204   1

etc. But I want result to in ordered way e.g.: The output should be order by bk.date_to desc

My query is as below its showing me count but if I use order by its not working

select bk.guest_no, bk.hotel_no, bk.room_no,
       count(bk.guest_no+bk.hotel_no+bk.room_no) as noOfTimesRoomBooked
from booking bk 
group by bk.guest_no, bk.hotel_no, bk.room_no, bk.date_to
order by bk.date_to desc

So with adding order by result is showing different , because as I added order by date_to column so i have to add this column is group by clause too which will end up in different result as below

    GUEST_NO    HOTEL_NO        ROOM_NO Count
    1                1              103   1
    1                1              104   1
    1                1              103   1
    1                1              101   1
    2                2              204   1

Which is not the output I want. I want these four column but with order by desc of date_to column and count as no of repetition of first 3 columns

Answer 1

I think a good way to do this would be grouping by guest_no, hotel_no and room_no, and sorting by the maximum (i.e. most recent) booking date in each group.

SELECT
    guest_no,
    hotel_no,
    room_no,
    COUNT(1) AS BookingCount
FROM
    booking
GROUP BY
    guest_no,
    hotel_no,
    room_no
ORDER BY
    MAX(date_to) DESC;