Могу ли я использовать неагрегатные столбцы с группой?

Вы не можете (не должны) помещать неагрегаты в строку SELECT запроса GROUP BY.

Однако я хотел бы получить доступ к одному из неагрегатов, связанных с макс. На простом английском я хочу таблицу с самым старым идентификатором каждого вида.

CREATE TABLE stuff (
   id int,
   kind int,
   age int
);

Этот запрос дает мне информацию, которую я ищу:

SELECT kind, MAX(age)
FROM stuff
GROUP BY kind;

Но это не в самой полезной форме. Я действительно хочу id связанный с каждой строкой, чтобы я мог использовать его в последующих запросах.

Я ищу что-то вроде этого:

SELECT id, kind, MAX(age)
FROM stuff
GROUP BY kind;

Это выводит это:

SELECT stuff.*
FROM
   stuff,
   ( SELECT kind, MAX(age)
     FROM stuff
     GROUP BY kind) maxes
WHERE
   stuff.kind = maxes.kind AND
   stuff.age = maxes.age

Похоже, что должен быть способ получить эту информацию без необходимости присоединяться. Мне просто нужен механизм SQL, чтобы помнить другие столбцы, когда он вычисляет макс.

Ответы

Ответ 1

Вы не можете получить идентификатор строки, найденной MAX, потому что не может быть только один идентификатор с максимальным возрастом.

Ответ 2

Вы не можете (не должны) помещать неагрегаты в строку SELECT запроса GROUP BY.

Вы можете и должны определить, по чему вы группируете, чтобы агрегатная функция возвращала правильный результат.

MySQL (и SQLite) в своей бесконечной мудрости решили, что они пойдут против спецификации, и позволят запросам принимать предложения GROUP BY без пропущенных столбцов в SELECT - это фактически делает эти запросы непереносимыми.

Похоже, что должен быть способ получить эту информацию без необходимости присоединяться.

Без доступа к аналитическим/ранжирующим/оконным функциям, которые не поддерживает MySQL, самостоятельное объединение с производным представлением таблицы/строки является наиболее переносимым средством получения желаемого результата.

Ответ 3

Я думаю, что на самом деле заманчиво попросить систему решить проблему за один проход, вместо того, чтобы дважды выполнять работу (найти макс и найти соответствующий идентификатор). Вы можете использовать CONCAT (как указано в Naktibalda, ссылающейся на статью), не уверен, что будет более эффективным

SELECT MAX( CONCAT( LPAD(age, 10, '0'), '-', id)
FROM STUFF1
GROUP BY kind;

Если вы работаете, вам нужно разделить ответ, чтобы получить возраст и идентификатор. (Это действительно уродливо, хотя)

Ответ 4

В последних базах данных вы можете использовать sum() over (parition by...) для решения этой проблемы:

select id, kind, age as max_age from (
  select id, kind, age, max(age) over (partition by kind) as mage
    from table)
where age = mage

Это может быть однопроходным

Ответ 5

У вас должно быть соединение, потому что агрегатная функция max извлекает много строк и выбирает макс. Поэтому вам нужно соединение, чтобы выбрать тот, который найдена функция agregate.

Иначе говоря, как бы вы ожидали, что запрос будет вести себя, если вы замените max суммой?

Внутреннее соединение может быть более эффективным, чем ваш дополнительный запрос.

Ответ 6

PostgesSQL DISTINCT ON будет полезен здесь.

SELECT DISTINCT ON (kind) kind, id, age 
FROM stuff
ORDER BY kind, age DESC;

Это группирует по виду и возвращает первую строку в упорядоченном формате. Как мы упорядочили по возрасту в порядке убывания, мы получим строку с максимальным возрастом для вида.

PS столбцы в DISTINCT ON должны появляться первыми в порядке