MYSQL GROUP BY and ORDER BY not working together as expected

Question

I will try to explain my question with two cases:

1. When I have used only ORDER BY clause in my query: See the 1st image.

   

2. When I have used both GROUP BY and ORDER BY claluse: see 2nd image

   

My requirement is to get only single record for each hotel no duplicacy, and second ordery by should be the lowest hotel price should be on TOP;

I mean record I need record which will pull third_party_rate as 1041.

You can view the MySQL code in text format as shown below:

SELECT h.hotel_id AS id,h.hotel_city AS city,h.hotel_name AS hotelname,h.hotel_star AS hotelcat,hwd.double_spl_rate, hwd.extra_bed_spl_rate,hwd.meal_plan_spl,hwd.third_party_rate,hwd.third_party_extra_bed, hwd.third_party_meal_plan,hwd.room_category,hrd.hotel_rate_from,hrd.hotel_rate_to FROM hotels_list AS h 
INNER JOIN hotel_rate_detail AS hrd ON h.hotel_id = hrd.hotels_id 
INNER JOIN hotel_week_days AS hwd ON hrd.hotel_id = hwd.h_id 
WHERE ( ('2015-07-02' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) OR ('2015-07-03' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) ) AND (h.hotel_city = '1')
AND (hwd.double_spl_rate != 0 OR hwd.third_party_rate != 0) AND (h.hotel_star <= '2') 
AND h.hotel_id = 364
GROUP BY h.hotel_id ORDER BY hwd.double_spl_rate,hwd.third_party_rate ASC;

Note that I have used only id = 364 on the above context just to show the result but there are many other repeated hotel ids with such cases, so I need a general query which works not only for id = 364 but for other ids as well so that I got complete result with one single query.

Answer 1

MySQL will allow these kinds of silly queries, where GROUP BY is assumed to contain all columns even though you don't specify them. Grouping is done first, so ordering does nothing if there is only one result.

You need to use MIN(third_party_rate) to get the lowest value for that column.