(no subject)

[kastaneda]

CREATE TABLE books (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  author varchar(20) NOT NULL,
  book varchar(30) NOT NULL,
  published year(4) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO books (id, author, book, published) VALUES
(1, 'John R. R. Tolkien', 'The Hobbit', 1937),
(2, 'John R. R. Tolkien', 'The Lord of the Rings', 1955),
(3, 'Terry Pratchett', 'The Colour of Magic', 1983),
(4, 'Terry Pratchett', 'Equal Rites', 1987),
(5, 'Terry Pratchett', 'The Truth', 2000);

SELECT * FROM books GROUP BY author возвращает строки 1 и 3.
Как получить строки 2 и 5?

Похоже, у меня пятница настала раньше времени, мозги не варят. ORDER BY published DESC тут несколько неуместен, поскольку сортирует уже сгруппированные записи. Пока придумал только вот такое: SELECT * FROM books WHERE id IN (SELECT MAX(id) FROM books GROUP BY author) — это грязный хак, записи могут идти не по порядку. Да и вообще, делать вложенный запрос ради такой ерунды…

Есть идеи?

Upd.: раз уж сегодня пятница, давайте соберём побольше альтернативных решений.
Чем монстрообразнее — тем лучше :)

Comments

Взбульк.

[fester-ua.livejournal.com]

Можно странноjoin на сам себя.

[kastaneda]

А как?

[gul-kiev.livejournal.com]

Например, так:
select b1.id, author, b1.book, b1.published from books as b1 join books as b2 using (author) group by b1.id, b1.author having b1.id = max(b2.id);

[kastaneda]

Ух ты! Класс.

[gul-kiev.livejournal.com]

btw, группировать достаточно по b1.id.
b1.author в "group by" лишний.

[kastaneda]

До меня внезапно дошло, зачем нужен HAVING.

[bo-dun.livejournal.com]

SELECT трампарампампам FROM (SELECT трампарампампам ORDER BY published DESC) GROUP BY author;

не?

[kastaneda]

mysql> select * from (select * from books order by published desc) x group by author;
+----+--------------------+-----------------------+-----------+
| id | author             | book                  | published |
+----+--------------------+-----------------------+-----------+
|  2 | John R. R. Tolkien | The Lord of the Rings |      1955 | 
|  5 | Terry Pratchett    | The Truth             |      2000 | 
+----+--------------------+-----------------------+-----------+
2 rows in set (0,00 sec)

Да, то что надо. Но громоздко до жути.

[kastaneda]

mysql> select * from (select * from books order by published desc) x group by author;
+----+--------------------+-----------------------+-----------+
| id | author             | book                  | published |
+----+--------------------+-----------------------+-----------+
|  2 | John R. R. Tolkien | The Lord of the Rings |      1955 | 
|  5 | Terry Pratchett    | The Truth             |      2000 | 
+----+--------------------+-----------------------+-----------+
2 rows in set (0,00 sec)

Да, действительно. Торможу.
Как бы теперь сделать запрос менее громоздким...

[diggya.livejournal.com]

create view book_reverse as select * from books order by id desc;
SELECT * FROM book_reverse GROUP BY author;

[kastaneda]

Неожиданно :)

[0ld.livejournal.com]

и получаем те же 1 и 3 только в обратном порядке :)

Баянистое.

[diggya.livejournal.com]

SELECT id, author, book, max(published) FROM `books` group by author;

Я тут подозреваю что ты пытаешься решить какую-то определенную задачу.

[kastaneda]

mysql> SELECT id, author, book, max(published) FROM `books` group by author;
+----+--------------------+---------------------+----------------+
| id | author             | book                | max(published) |
+----+--------------------+---------------------+----------------+
|  1 | John R. R. Tolkien | The Hobbit          |           1955 | 
|  3 | Terry Pratchett    | The Colour of Magic |           2000 | 
+----+--------------------+---------------------+----------------+
2 rows in set (0,00 sec)

Ну, ты знаешь же, тут не те строки. Должно быть не 1 и 3, а 2 и 5.
Задача очень близка к реальности, кстати.

[unbe.livejournal.com]

Использовать group by без агрегатных функций немного странно. Что нужно получить, последнюю книгу автора?
А то строки 2 и 5 получить можно и так: SELECT * FROM books WHERE id IN (2,5)

[kastaneda]

Угу, последнюю книгу (ORDER BY published DESC).
Без вложенного запроса вряд ли получится, выше в комментах есть работающий вариант.

[unbe.livejournal.com]

вложенный запрос действительно нужен, хотя на оракле возможно удастся и без.
работающий вариант это этот? http://kastaneda.livejournal.com/207905.html?thread=2198305#t2198305
он может и работает, но неправильное, а правильное, как всегда, в документации
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[kastaneda]

Да, пример выше работает. По крайней мере, он работает на моём MySQL'е.

mysql> SELECT * FROM books b1 WHERE published=(
SELECT MAX(published) FROM books b2 WHERE b1.author=b2.author GROUP BY author);
+----+--------------------+-----------------------+-----------+
| id | author             | book                  | published |
+----+--------------------+-----------------------+-----------+
|  2 | John R. R. Tolkien | The Lord of the Rings |      1955 | 
|  5 | Terry Pratchett    | The Truth             |      2000 | 
+----+--------------------+-----------------------+-----------+
2 rows in set (0,01 sec)

Ух ты. Это… изящно.

[zmeuka.livejournal.com]

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

[kastaneda]

Оффтоп: слушай, а ты, случайно, не учился в киевском физмате?

[unbe.livejournal.com]

дык, учился

[kastaneda]

(* до меня доходит, как до жирафа *)
Ага! Тогда я тебя знаю.

[unbe.livejournal.com]

а ваще нет, не учился :)
т.е. я учился с тобой, но не в физмате, а в кубике.

[zmeuka.livejournal.com]

Ну раз ты попросил пятничного монстра, то теперь ломай голову, как это работает:

select a1.id, a1.author, a1.book, a1.published from books a1 left join books a2 on a1.author=a2.author and a1.published < a2.published where a2.id is null;

+----+--------------------+-----------------------+-----------+
| id | author | book | published |
+----+--------------------+-----------------------+-----------+
| 2 | John R. R. Tolkien | The Lord of the Rings | 1955 |
| 5 | Terry Pratchett | The Truth | 2000 |
+----+--------------------+-----------------------+-----------+
2 rows in set (0.00 sec)

Я знаю, как. Но логика не совсем очевидна.

[fester-ua.livejournal.com]

Бугагирую. Изящно :)

[kastaneda]

Вот это как раз самый простой и понятный пример из всех :)