Strange MySQL GROUP BY/ORDER BY behaviour

Question

        SELECT
            p.price,
            h.name AS hotel_name
        FROM
            prices p
            LEFT JOIN hotels h ON p.hotel_id = h.id
        WHERE
            p.city = 'boedapest'
            AND p.hotel_id IS NOT NULL
        GROUP BY p.name     
        ORDER BY p.price ASC

RESULT:
26 Eben Hotel Budapest
27 Veritas
28 Ibis Budapest Heroes Square
29 Hunguest Hotel Griff
30 Hotel Thomas
31 NH Budapest
31 Rubin Wellness and Conference Hotel
32 Benczur Hotel
33 Atlantic
33 Delibab Hotel Budapest

Exactly the same result with the GROUP BY commented out:
24 Gerand Hotel Ventura
25 Hunguest Hotel Platanus
26 Boulevard City Guesthouse
26 Tulip Inn Millennium Budapest
26 Eben Hotel Budapest
27 Veritas
27 Baross
28 Hotel Thomas
28 Hunguest Hotel Griff
28 Ibis Budapest Heroes Square

Basically I would like to GROUP by name, but keep the ORDER BY. Since 24 is less than 26, I kind of expect it to show up earlier than 26. Also keep in mind, I'm displaying only the first 10 results, to keep the post short.

I'm using: SELECT VERSION() -> 5.1.37

Answer 1

Add HAVING MIN(p.price).

Answer 2

You are using a group by on p.name, but selecting that field and another one ; and why are you using a group by here ? Is it really necessary ?

Quoting the SELECT page of the MySQL manual :

MySQL extends the use of GROUP BY to allow selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 11.12, “Functions and Modifiers for Use with GROUP BY Clauses”.

And reading 11.12.3. GROUP BY and HAVING with Hidden Columns might help :

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Basically, adding an aggregate function on the p.price column somewhere might help, I'd say.

Answer 3

Group By clause is mostly used when you need result with some aggregate functions like (sum, avg etc.)

another thing is you need to select column in your result set which you want to be group by.

Answer 4

In case someone else might struggle over this:

Add a SELECT MIN(p.price) AS price and ORDER BY price. This will give you the desired result.