Выберите 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 последних записи (по времени), чей otheridentifier отличается. Таким образом, в этом случае результат будет id s: 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.

Ответы

Ответ 1

Он не возвращает то, что вы ожидаете, потому что группировка происходит до упорядочения, что отражается в позиции предложений в инструкции 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

Ответ 2

Вы можете присоединиться к таблице непосредственно для фильтрации последней записи на 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

Ответ 3

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

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t 
GROUP BY t.otheridentifier

Ответ 4

SELECT * FROM table t1 
WHERE t1.time = 
    (SELECT MAX(time) FROM table t2 
     WHERE t2.otheridentifier = t1.otheridentifier)

Ответ 5

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

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

select *
from   `table` t1
where  t1.`time` in (
                    select   max(s2.`time`)
                    from     `table` t2
                    group by t2.otheridentifier
                    )

Ответ 6

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

SELECT * FROM 
      (SELECT * FROM `table` order by time DESC)
          t group by otheridentifier

Ответ 7

как насчет

SELECT *, max(time) FROM `table`  group by otheridentifier

Ответ 8

Это также:

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.