Выберите 3 последних записи, в которых значения одного столбца различны.

У меня есть следующая таблица:

    id       time      text      otheridentifier
    -------------------------------------------
    1        6         apple     4
    2        7         orange    4
    3        8         banana    3
    4        9         pear      3
    5        10        grape     2

Что я хочу сделать, так это выбрать 3 последних записи (по убыванию времени), чьи otheridentifiers различны. В этом случае результатом будет id: 5, 4 и 2.

id = 3 будет пропущено, потому что есть более свежая запись с тем же полем otheridentifier.

Вот что я пытался сделать:

SELECT * FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3

Однако я получаю строки с id = 5, 3 и 1 вместо 5, 4, 2, как ожидалось.

Может ли кто-нибудь сказать мне, почему этот запрос не вернул то, что я ожидал? Я попытался изменить ORDER BY на ASC, но это просто изменило порядок возвращаемых строк на 1, 3, 5.


mysql group-by sql-order-by
person atp    schedule 29.05.2009    source источник

Ответы (8)


arrow_upward
34
arrow_downward

Он не возвращает то, что вы ожидаете, потому что группировка происходит до упорядочения, что отражено положением предложений в операторе SQL. К сожалению, чтобы получить нужные строки, вам придется стать более привлекательным. Попробуй это:

SELECT *
FROM `table`
WHERE `id` = (
    SELECT `id`
    FROM `table` as `alt`
    WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
    ORDER BY `time` DESC
    LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3
person chaos    schedule 29.05.2009
comment
Я вспомнил, сколько часов потратил на исправление подобного sql, и оказалось, что mysql 4.0 не поддерживает вложенные запросы; p - person Unreality; 29.05.2009
comment
@Unreality: К счастью, большинство решений, включающих подзапросы, при необходимости могут быть выражены как соединения. :) - person Rytmis; 29.05.2009
comment
По возможности избегайте подзапросов, потому что они медленные. Вместо этого используйте LEFT JOIN :) - person marknt15; 29.05.2009
comment
@Jasie: dev.mysql.com/doc :) @ marknt115: Да, старайтесь избегать их насколько возможно, но не более того. - person chaos; 29.05.2009
comment
@ marknt15, никакие подзапросы не медленные только потому, что они подзапросы. См. percona.com/blog/2010/ 18 марта / когда-под-выбор-работает-быстрее - person Pacerier; 09.04.2015

arrow_upward
18
arrow_downward

Вы можете присоединиться к таблице, чтобы отфильтровать последнюю запись по otheridentifier, а затем взять верхние 3 строки из нее:

SELECT last.*
FROM `table` last
LEFT JOIN `table` prev 
    ON prev.`otheridentifier` = last.`otheridentifier`
    AND prev.`time` < last.`time`
WHERE prev.`id` is null
ORDER BY last.`time` DESC 
LIMIT 3
person Andomar    schedule 29.05.2009
comment
Андомар, не могли бы вы объяснить, как MySQL использует таблицу prev в вашем запросе? Я попытался использовать это в локальном запросе и получил сообщение об ошибке «database.prev» не существует. - person a coder; 29.01.2013
comment
Поиграйте с данными OP и вашим запросом. Не знаю, почему за этот ответ проголосовали 6 раз - здесь он не работает: sqlfiddle.com/#! 2 / ace0b / 1 - person a coder; 29.01.2013
comment
@acoder: Вы правы, в запросе была небольшая ошибка: отсутствовало имя таблицы после left join. Я обновил ответ. - person Andomar; 29.01.2013
comment
Теперь работает нормально - sqlfiddle.com/#!2/ace0b/6. Спасибо за ответ и обновление. - person a coder; 29.01.2013
comment
Похоже, вы дублировали нежелательные результаты OP. Кажется, что переключение меньше чем на большее, чем работает, но это вместе с большинством других ответов, похоже, сильно усложняет ситуацию. ЗАКАЗ ПО МАКСУ (время) - САМОЕ ПРАВИЛЬНОЕ решение. - person sequentiallee; 31.03.2013
comment
Не согласен с @Lee. Это наиболее эффективное решение. - person kayue; 08.11.2013
comment
Спасибо @Lee, это было самое простое решение (добавление MAX ())! - person kfriend; 22.05.2015

arrow_upward
4
arrow_downward

У меня было аналогичное требование, но у меня были более продвинутые критерии отбора. Используя некоторые другие ответы, я не смог получить именно то, что мне нужно, но обнаружил, что вы все еще можете сделать GROUP BY после и ORDER BY следующим образом:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t 
GROUP BY t.otheridentifier
person 11101101b    schedule 30.04.2013

arrow_upward
2
arrow_downward

Ответ Андомара, вероятно, лучше всего, поскольку он не использует подзапрос.

Альтернативный подход:

select *
from   `table` t1
where  t1.`time` in (
                    select   max(s2.`time`)
                    from     `table` t2
                    group by t2.otheridentifier
                    )
person mechanical_meat    schedule 29.05.2009
comment
Я думаю, что вижу здесь проблему, если значения времени не уникальны - это может возвращать строки, которых не должно быть. Предположим, есть значение времени, которое является максимальным для одного другого идентификатора, но, скажем, вторым по величине для другого другого идентификатора. Разве этот запрос не вернет оба других идентификатора? Хотя, может, я и вовсе отключился, я все еще немного устал. :) - person Rytmis; 29.05.2009
comment
@Rytmis: Да, и мой вопрос тоже, и ваш :) хе-хе - person Andomar; 29.05.2009
comment
@Andomar: Хм, а ты уверен в моем вопросе? Поскольку я только что протестировал его, добавив строку (6, 7, 'клубника', 3) - значение времени 7 является наибольшим в группе, имеющей другой идентификатор 4, но вторым по величине в группе, имеющей другой идентификатор 3. My query по-прежнему возвращает только те строки, которые хотел OP. Мой тестовый пример неверен? :) - person Rytmis; 29.05.2009
comment
@Andomar: Нет, тестовый пример правильный - этот запрос возвращает строку «клубника», а мой - нет. - person Rytmis; 29.05.2009
comment
@Rytmis: попробуйте с (6,7, Strawberry, 4) - person Andomar; 29.05.2009

arrow_upward
2
arrow_downward

Вы можете использовать этот запрос, чтобы получить правильный ответ: 

SELECT * FROM 
      (SELECT * FROM `table` order by time DESC)
          t group by otheridentifier
person php    schedule 08.01.2014

arrow_upward
1
arrow_downward

как насчет

SELECT *, max(time) FROM `table`  group by otheridentifier
person mbo    schedule 29.02.2012
comment
Изменить - это работает с данными OP. В моем запросе было несколько дополнительных соединений. Это отлично работает с данными OP. sqlfiddle.com/#!2/ace0b/2 - person a coder; 29.01.2013
comment
Кажется, это не работает. В вашем sqlfiddle он должен показывать апельсин и грушу - их временная ценность выше. - person mahemoff; 19.05.2013

arrow_upward
0
arrow_downward

Это также:

SELECT * FROM
OrigTable T INNER JOIN 
( 
SELECT otheridentifier,max(time) AS duration
FROM T
GROUP BY otheridentifier) S
ON S.duration = T.time AND S.otheridentifier = T.otheridentifier.
person user2450223    schedule 15.06.2016

arrow_upward

arrow_downward

person    schedule
comment
Как это выбрать последнюю строку для другого идентификатора? - person Andomar; 29.05.2009
comment
@Andomar: Я не должен пытаться отвечать на вопросы, когда я не полностью проснулся. Немного изменили имена столбцов - посмотрим, имеет ли это больше смысла. :) - person Rytmis; 29.05.2009