MySQL GROUP BY kills ORDER BY

Question

I have a problem and there are already some similar questions but they didn't solve my problem.

I have several hotels and pictures in it. The pictures are ordered by a number. I want all hotels and the picture with the lowest number.

When I try following query I get the right order:

SELECT s25.entry_id AS id, 
s25.value AS title, 
s35.file AS picture, 
s86.value AS picture_sort 
FROM sym_entries_data_25 AS s25 
LEFT JOIN sym_entries_data_34 AS s34 ON (s25.entry_id = s34.relation_id)     
LEFT JOIN sym_entries_data_35 AS s35 ON (s34.entry_id = s35.entry_id) 
LEFT JOIN sym_entries_data_86 AS s86 ON (s34.entry_id = s86.entry_id) 
ORDER BY s86.value

I will get the Array

[0] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis2.jpg
        [picture_sort] => 1
    )

[1] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis1.jpg
        [picture_sort] => 2
    )

[2] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis3.jpg
        [picture_sort] => 3
    )

...And so on

But I only want one result per hotel, so I tried a GROUP BY:

SELECT s25.entry_id AS id, 
s25.value AS title, 
s35.file AS picture, 
s86.value AS picture_sort 
FROM sym_entries_data_25 AS s25 
LEFT JOIN sym_entries_data_34 AS s34 ON (s25.entry_id = s34.relation_id)     
LEFT JOIN sym_entries_data_35 AS s35 ON (s34.entry_id = s35.entry_id) 
LEFT JOIN sym_entries_data_86 AS s86 ON (s34.entry_id = s86.entry_id) 
GROUP BY s25.value
ORDER BY s86.value

Then I only get one Result but a random one, not the first one:

[0] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis3.jpg
        [picture_sort] => 3
    )

What could be the problem here?

Search for similar questions under the tag [tag:greatest-n-per-group]. `GROUP BY` could help if used with care but most of the times the correct solution for this kind of problem doesn't use it. — axiac, Dec 29 '15 at 18:31
By grouping by "Title" you can expect to only get one result per distinct "Title" value. Since you have not used aggregate functions on any of the other fields, the values selected from the rows with that "Title" value are officially indeterminate. Many RDBMS (that are not MySQL) won't even accept `GROUP BY` queries that do not group on all non-aggregated fields. — Uueerdo, Dec 29 '15 at 18:32
I worry about a schema design that has tables called things like xxx25. It seems like things have gone very, very wrong :-( — Strawberry, Dec 29 '15 at 18:35
@Strawberry The schema design isnt by me. Its by symphony cms and if you know it, it makes a lot of sense the way it is. — Niko Lang, Dec 29 '15 at 18:45
The correct solution for your problem requires adding a carefully-crafted `LEFT JOIN` to your original query. However, because of the names of the tables and columns it's difficult to tell what each table contains and how they are joined. Read [this answer](http://stackoverflow.com/a/28090544/4265352) and try to work out your solution using the explanation provided there. — axiac, Dec 29 '15 at 19:08

score 1 · Answer 1 · answered Dec 29 '15 at 18:36

1

when you are doing group by, you have no guarantee regarding the values of the columns you are not grouping by. for example if you have a table with columns a and b, and data like:

a | b
-----
1 | 2
1 | 3

if you'll do select * from table group by a, you can get either 2 or 3 as the value of b.

if you only want the top result for the hotel, what you need to do is not group by, but take the result where picture_sort equals to the min(picture_sort)

answered Dec 29 '15 at 18:36

Nir Levy

12,750
3
21
38

Like this? WHERE picture_sort = MIN(picture_sort) I get the error message "Unknown column 'picture_sort' in 'where clause'" – Niko Lang Dec 29 '15 at 18:58
`SELECT * ... GROUP BY` is not even valid SQL. `MySQL` [allows it](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) until version 5.7.5 but it reserves itself the right to pick whatever values it wants for all the columns that appear in the `SELECT` clause (not used in a [`GROUP BY` aggregate function](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html)) and do not appear in the `GROUP BY` clause. – axiac Dec 29 '15 at 19:03
Tried it now with HAVING picture_sort = MIN(picture_sort). No Error Message now but I get no results back. Any ideas? – Niko Lang Dec 29 '15 at 20:44

MySQL GROUP BY kills ORDER BY

1 Answers1