Понять MAX и GROUP BY по-человечески, а не языком mysql

[Avenus]

MAX и GROUP BY

Здравствуйте!
Изучил документацию mysql по MAX и GROUP BY, а также обсуждения на форуме, которые выдал мне поиск по запросу "max group". В принципе, проблема почти такая же. Но во всех примерах идет выборка что-то вроде:

select id,max(rate) from table group by id

Такой пример мне понятен и выдает только те id у которых максимальный rate.
В моем случае немного по-другому. Прошу даже больше объяснить человеческим языком, а не языком mysql почему это именно так работает.
Ситуация:
Есть таблица TABLE и FOTO . Вот некоторые записи из таблицы TABLE:
id=1, event=1, rate=0, name=a ...
id=2, event=2, rate=10, name=b ...
id=3, event=2, rate=0, name=c ...
id=4, event=3, rate=0, name=d ...
...
Необходимо выбрать только те строки со всеми полями из этой таблицы TABLE, у которых MAX(rate) среди групп по event и присоединить с помощью LEFT JOIN строки из другой таблицы FOTO к выбранным из TABLE.
Делаю так:

select max(table.rate) as rate,table.*,foto.* from table left join foto on foto.id=table.id group by table.event order by rate desc

Получаю:
id=1, event=1, rate=0, name=a ...
id=3, event=2, rate=0, name=c ... rate=10 ??? ведь rate=10 у строки с id=2
id=4, event=3, rate=0, name=d ... rate=0
Необходимо как видно из условия получить:
id=1, event=1, rate=0, name=a ...
id=2, event=2, rate=10, name=b ...
id=4, event=3, rate=0, name=d ...
Спасибо всем, кто объяснит или поможет сделать необходимый результат!

 

[Gas]

когда идёт группировка, в результате ты можешь получить значения столбца по которому группируется и результаты агрегатных функций (min,max,avg...). Mysql допускает (если не sql_mode='ONLY_FULL_GROUP_BY') указывать в select'е поля, которые не подчинаются этому правилу, но значения их будет непрогнозируемыми.

Тут решения

 

[MiksIr]

> Такой пример мне понятен и выдает только те id у которых максимальный rate.
Понятен ли? Этот пример выдает все id и максимальное значение rate среди записей с одинаковым id.

 

[Avenus]

Gas
Пытался так сделать, но вложенные запросы по-видимому не работают почему-то. Т.к. вообще ошибки появляются.

-~{}~ 01.11.07 22:07:

MiksIr
Извиняюсь, проверил, действительно так как вы сказали

Кстати, заметил в документации mysql, что решение которое я пробовал только начиная с версии 4.1 mysql...
Установил эту версию - теперь работает как надо
Добавил: ... where rate=(select max(temp.rate) from table as rate)
Спасибо!

-~{}~ 08.11.07 15:05:

Выяснилось, что неправильно все-таки это работает. Когда увеличивается RATE для какого-либо из EVENT, то выбираются только те строки, у которых максимальный RATE игнорируя разные EVENT.

Т.е.:
id=1, event=1, rate=0, name=a ...
id=2, event=2, rate=10, name=b ...
id=3, event=2, rate=0, name=c ...
id=4, event=3, rate=0, name=d ...

Запрос:
select table.* FROM table WHERE table.rate=(SELECT max(temp.rate) FROM table AS temp) GROUP BY event

Получаем только:
id=2, event=2, rate=10, name=b ...

Что делать, как сделать правильный запрос?

-~{}~ 08.11.07 15:07:

Когда RATE у всех 0 - выдает строки с разными EVENT как положено.

-~{}~ 08.11.07 16:14:

Ну наконец-то, нашел пример рабочий!!!

SELECT t1.* FROM table AS t1,
(SELECT event,MAX(rate) AS maxrate FROM table GROUP BY event) AS t2
WHERE t1.rate=t2.maxrate group by t1.event