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

I used a separate method on php to get the id's and put it into an array. Then on another sql query i searched the data by looking up the id's

$ids  = array();

$sql = "SELECT pid FROM table_name  WHERE DATE(column_name)='yyyy-mm-dd'  ORDER BY column_name ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

if (!in_array($row['value'], $ids)) {
    array_push($ids, $row['value']);
}
    }
}

And then

$sql = "SELECT * FROM table_name WHERE id IN (".implode(',', $ids).") ORDER BY column_name ASC";

Answer 2

In SQL grouping is done first after that your ordering is done.

So in this case you can order your record first. group the resulting with your specific column it will returns you a given record whatever you want. you can used you query like. replace your query as shown below:

e.g

select * from 
  (select * from table_name order by column_name Asc) 
  as t group by t.column_name_for_group

Your query as I think this can help you to achive your record:

Select * from (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
ORDER BY hwd.double_spl_rate,hwd.third_party_rate ASC) as result_table GROUP BY result_table.id;

Answer 3

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.