Как преобразовать оператор "не в" для объединения с группой

Question

Как получить все записи, кроме самых новых, сгруппированных по record_id?

Таблица:

attachments
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| record_id | int(11)          | NO   |     | NULL    |                |
| created   | date             | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

Исходные тестовые строки в таблицах вложений:

mysql> select * from attachments;
+----+-----------+------------+
| id | record_id | created    |
+----+-----------+------------+
|  1 |         1 | 2014-11-05 |
|  2 |         1 | 2014-11-04 |
|  3 |         1 | 2014-11-03 |
|  4 |         1 | 2014-11-02 |
|  5 |         2 | 2014-11-05 |
|  6 |         2 | 2014-11-04 |
|  7 |         2 | 2014-11-03 |
+----+-----------+------------+

Я создал этот запрос, но он слишком медленный для больших таблиц:

SELECT * FROM attachments WHERE id NOT IN (
SELECT 
attachments.id
FROM attachments
GROUP BY attachments.record_id
ORDER BY attachments.`created` DESC
)

Результат:

+----+-----------+------------+
| id | record_id | created    |
+----+-----------+------------+
|  2 |         1 | 2014-11-04 |
|  3 |         1 | 2014-11-03 |
|  4 |         1 | 2014-11-02 |
|  6 |         2 | 2014-11-04 |
|  7 |         2 | 2014-11-03 |
+----+-----------+------------+

Мой запрос на присоединение получит другой результат, выберите только самые новые, сгруппированные по record_id:

SELECT  old.*
FROM attachments `old`
INNER JOIN attachments `new` ON `new`.id = `old`.id
GROUP BY new.record_id
ORDER BY old.`created` DESC

Результат:

+----+-----------+------------+
| id | record_id | created    |
+----+-----------+------------+
|  1 |         1 | 2014-11-05 |
|  5 |         2 | 2014-11-05 |
+----+-----------+------------+

Изменение INNER на RIGHT или LEFT не меняет результат на выходе.

Answer 1

Select *
From attachments a
Where exists (Select *
              From attachments a2
              Where a.record_id = a2.record_id
              and   a.created < a2.created);

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

Answer 2

Попробуйте этот запрос:

SELECT t1.* FROM attachments t1 
left outer join
(SELECT attachments.id
FROM attachments
GROUP BY attachments.record_id
ORDER BY attachments.`created` DESC) t2 on t1.id = t2.id
where t2.id is null;

SQL Fiddle