Форум пользователей MySQL

Наверное
SELECT * from t1 order by id, code desc, date desc;
Читать курсором и с помощью IF выбирать то, что надо и записывать в новую таблицу.

Можно еще такое решение:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD UNIQUE(id);
INSERT INTO t2 SELECT * from t1 order by id, code desc, date desc ON DUPLICATE KEY UPDATE SET code=code;

Если без копирования, то надо через подзапросы, что эквивалентно копированию. Кроме того, удобно будет воспользоваться уникальностью поля code, возмножно удастся во временную таблицу заносить не все данные, а только значения code.

code - по сути и есть примари, я чуть повыше написал об этом.

Вот родилось такое решение, насколько оно отражает чего я хочу? ничего я не упустил?

DELETE *
FROM t1,
(
  SELECT id, COUNT(id) AS cnt, MAX(date) AS date, MAX(code) AS code // достаем максимумы и количество повторов
  FROM t1
  GROUP BY id
  HAVING cnt>1  // оставляем только те, которые повторяются
) AS t2               // и join-им с исходной таблицей
  WHERE 
      t1.id=t2.id    // по id
  AND 
     (t1.date!=t2.date OR t1.code!=t2.code)  // прибивая все, дата которых не совпадает с max или, если дата совпала то если не совпал code

беспокоит внутренний запрос, тяжеловат наверно для большой таблицы?
ну и наверно надо сделать индексы date, code и id

LazY написал:

Вообще, если запрос исправить (сейчас есть несколько ошибок), то, скорее всего, при таком запросе будет Error using GROUP BY

Alias'ы Вы, конечно, жестко выбираете одноименные с именами столбцов...

А в чем ошибки?

Можно конечно чуть покрасивше переписать:

DELETE FROM t1,
(
  SELECT id, COUNT(id) AS cnt, MAX(date) AS maxdate, MAX(code) AS maxcode
  FROM t1
  GROUP BY id
  HAVING cnt>1
) AS t2               
  WHERE 
      t1.id=t2.id    
  AND 
     (t1.date!=t2.maxdate OR t1.code!=t2.maxcode)

но суть то от этого не изменится... а что не так с group by?

LazY написал:

> А в чем ошибки?
А Вы попробуйте его выполнить
вообще не совсем понятна конструкция (SELECT ...) AS t2
во-первых, в таких местах подзапросы нельзя использовать
во-вторых, даже если бы было можно, то он должен был бы быть скалярным; а у Вас табличный

Эээ, тут вы не правы, вот ссылка: http://dev.mysql.com/doc/refman/5.1/en/ … views.html

Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON  clause of a JOIN operation.

В моем запросе, подзапрос является по сути самостоятельным запросом, который возвращает таблицу никак не свзанную с внешним запросом, в explain  этот запрос так и обозначается - derrived sub.
Дальше происходит просто JOIN с исходной таблицей и все WHERE реботают уже с этим JOIN, но никакого отношения не имеют к агрегирующим функциям внутри подзапроса.

LazY написал:

Ошибки с GROUP BY возникают, когда начинаются попытки использовать в условиях WHERE и HAVING непосредственно значения столбцов, отличных от тех, по которым группировка

т.е. не проходят запросы вида
SELECT 1 FROM t1 WHERE code != MAX(code) GROUP BY id
или
SELECT 1 FROM t1 GROUP BY id HAVING code != MAX(code)

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

Это понятно, именно поэтому у меня и не получается выкрутится одним запросом

"Вот черт" - подумал я сначала, - "как все просто"

а потом понял, что всех ввел в заблуждение чуть-чуть неверно написав исходную задачу, и сказал "вот черт" еще раз...

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

сначала нужно проверить ДАТУ и если она совпадает - то тогда берется наибольший код (т.к. он уникальный - то он всегда будет)

извиняюсь

paulus написал:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY `date`)

Так не получится, так как дату имеет смысл сравнивать, только если id совпадает.

paulus написал:

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY `date`)

Или я чего-то не понял?

Скорее забыл

id потерялся. а основная задача удалить дубликаты id

Как я понял надо для каждого id оставить только строчки с максимальной датой, а среди них - только строчку с максимальным кодом.

DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)
DELETE FROM t1 tt WHERE `date` NOT IN (SELECT MAX(`date`) FROM t1 WHERE id = tt.id)

Вот так?

вариант paulus похож на правду - это не два варианта, это 2 последовательных запроса. Правда второй запрос еще медленнее, чем перебор таблицы.

paulus написал:

1. DELETE FROM t1 WHERE code NOT IN (SELECT MAX(code) as code FROM t1 GROUP BY id, `date`)
2. DELETE FROM t1 tt WHERE `date` NOT IN (SELECT MAX(`date`) FROM t1 WHERE id = tt.id)

Вот так?

В принципе идею понял. Даже не думал, что можно зайти с другой стороны, спасибо

Но, что то мне подсказывает, что работать это будет ооочень медленно.
Во первых строк у меня > 1 000 000
Количество повторов, как я уже говорил менее 2 процентов. Получается что подзапрос в п. 1 по сути вынет всю! таблицу.
Это подсказывает мне и explain (пример на аналогичной маленькой табличке, строк - 16462):

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    t1    ALL                    16462    Using where
2    DEPENDENT SUBQUERY    t1    index        ix_id_code_date    111        16462    Using index

Видно что перебираться будут все строки хоть и по идексу, но опять же, этот составной индекс нужно создать только для цели этого удаления, потому что более он нигде применятся не будет,  отдельные индексы вообще не используются, так как у них очень низкая релевантность (поля - id и date почти! уникальны, code - pk).
Т.е. как мне кажется вынимать в подзапросе то что НЕ нужно удалять - не очень эффективно в моем случае. И это только первый запрос!
Со вторым запросом скорее всего аналогичная ситуация, так как NOT IN ()

Мой вариант пока выглядит как то пооптимистичнее, даже без составного индекса ix_id_code_date )

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    <derived2>    ALL                    607    ""
1    PRIMARY    t1    ref    ix_id    ix_id    103    t2.id    1    Using where
2    DERIVED    t1    index        code    111        16462    Using index; Using temporary

Отредактированно Shopen (28.11.2007 01:42:08)