MySQL - контроль, какая строка возвращается группой

У меня есть таблица базы данных следующим образом:

id    version_id    field1    field2
1     1             texta      text1
1     2             textb      text2
2     1             textc      text3
2     2             textd      text4
2     3             texte      text5

Если вы этого не сделали, он содержит несколько версий строки, а затем некоторые текстовые данные.

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

Я пробовал использовать группу по порядку по версии_ID DESC - но, похоже, он упорядочивается после ее сгруппировки, поэтому это не работает.

У кого-нибудь есть идеи? Я не могу поверить, что это невозможно!

UPDATE:

Придумайте это, что работает, но использует подзапрос:

SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id

Ответы

Ответ 1

Он называл выбор группового максимума столбца. Вот несколько разных подходов для mysql.

Вот как бы я это сделал:

SELECT *
FROM (SELECT id, max(version_id) as version_id FROM table GROUP BY id) t1
INNER JOIN table t2 on t2.id=t1.id and t1.version_id=t2.version_id

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

Это недостаток SQL, который вы более или менее должны использовать подзапрос для этого типа проблем (semi-joins - еще один пример).

Подзапросы не очень оптимизированы в mysql, но некоррелированные подзапросы не так уж плохи, пока они не настолько огромны, что они будут записаны на диск, а не в память. Учитывая, что в этом запросе есть только два ints, подзапрос может составлять миллионы строк задолго до того, как это произойдет, но подзапрос select * в вашем первом запросе может пострадать от этой проблемы гораздо раньше.

Ответ 2

Я думаю, что это сделало бы это, не уверен, что это лучший или быстрый, хотя.

SELECT * FROM table 
WHERE (id, version_id) IN 
  (SELECT id, MAX(version_id) FROM table GROUP BY id)

Ответ 3

SELECT id, version_id, field1, field2
FROM (
    SELECT @prev = id AS st, (@prev := id), m.*
    FROM (
           (SELECT @prev := NULL) p,
           (
            SELECT *
            FROM   mytable
            ORDER BY
                   id DESC, version_id DESC
           ) m
     ) m2
WHERE NOT IFNULL(st, FALSE);

Нет подзапросов, один проход на UNIQUE INDEX ON MYTABLE (id, version_id), если у вас есть (что, я думаю, вам стоит)

Ответ 4

Это псевдокод, но что-то вроде этого должно работать просто отлично

select *
from table
inner join
(
    select id , max(version_id) maxVersion
    from table 
) dvtbl ON id = dvtbl.id && versionid = dvtbl.maxVersion

Ответ 5

Обычно я делаю это с помощью подзапроса:

выберите id, version_id, field1, field2 из datatable как dt, где id = (выберите id из datatable, где id = dt.id order by version_id desc limit 1)

Ответ 6

Этот запрос выполнит задание без группы:

SELECT * FROM table AS t
LEFT JOIN table AS t2 
    ON t.id=t2.id 
    AND t.version_id < t2.version_id
WHERE t2.id IS NULL

Ему не нужны временные таблицы.

Ответ 7

Я думаю, что это то, что вы хотите.

select id, max(v_id), field1, field2 from table group by id

Результаты, полученные из этого,

1, 2, textb, text2

2, 3, texte, text5

Edit: Я воссоздал таблицу и ввел те же данные с идентификатором, что version_id является составным первичным ключом. Это дало ответ, который я дал ранее. Это также было в MySQL.

Ответ 8

не тестировал его, но что-то вроде этого могло бы работать:

SELECT * FROM table GROUP BY id ORDER BY MAX (version_id) DESC