Какой SQL запрос выведет самые часто встречающиеся записи?

Question

[Данил Самодуров]

Здравствуйте. Есть тестовая БД библиотеки, которую я сам создал. Есть две таблицы: BOOKS и POSSESSION. Первая хранит информации о книгах(наименование, автор, год издания, издательство и количество книг в библиотеке). Во второй информации о том, кто, какую книзу когда взял и сдал. Модель данных на рисунке:

Мне нужно узнать самого популярного автора за год. Собственно вот какой запрос я сделал:

SELECT AUTHOR
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
ORDER BY COUNT(AUTHOR) DESC
LIMIT 1;

Однако я сам понимаю, что если таких авторов будет два, то запрос не вернет правильное значение. Напрашивается вывод, что нужен какой-то подзапрос с агрегатной функцией MAX, но из-за того, что давно не работал с SQL не понимаю как и куда его вставить. Можете подсказать?

Comments

[Slava Rozhnev]

То есть Вы хотите в случае 2 равных по популярности авторов получить обоих? (две записи?)

[Slava Rozhnev]

Кроме этого опубликуйте схему таблиц. Так жедающим будет легче Вам помочь

[Данил Самодуров]

Slava Rozhnev, да, если такой случай

[Данил Самодуров]

Slava Rozhnev, модель данных добавил

[Slava Rozhnev]

Данил Самодуров, Спасибоб однако лучше публиковать таблицы как DDL (CREATE TABLE Books (...))

[Данил Самодуров]

Slava Rozhnev, точно, недопонял, простите.

[Slava Rozhnev]

Данил Самодуров, Data Definition Language (DDL) - это () так:

create table POSSESSION (
  	BOOK_ID INT,
	TAKING_DATE DATE
);

create table BOOKS (
	ID INT,
  	AUTHOR VARCHAR
);

[mletov]

В схеме не учтен случай когда одну книгу пишут несколько авторов

Answer 1

[nozzy]

select 
t1.AUTHOR,
t1.total
from
(
SELECT AUTHOR, count(*) as total
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
)  t1
where t1.total =
(
select
max(t2.total)
from
(
SELECT AUTHOR, count(*) as total
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
) t2
)

Comments

[Данил Самодуров]

Не подходит. Кстати, нужно еще добавить GROUP BY t1.AUTHOR, иначе ошибка.
И в итоге получиться то, что у меня. И мне не нужно больше столбцов кроме author в выводе.

[nozzy]

Данил Самодуров, я идею подсказал, а так там длинный запрос будет, если с подзапросом. Сейчас попробую

[nozzy]

Данил Самодуров, если уж так надо то поправил с подзапросом

[Данил Самодуров]

Тоже работает. Спасибо

Answer 2

[Slava Rozhnev]

Начиная с PostgreSQL 13 можно использовать новый функционал FETCH вместо LIMIT :

SELECT AUTHOR
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
ORDER BY COUNT(AUTHOR) DESC
FETCH FIRST 1 ROWS WITH TIES;

PostgreSQL fetch online

Comments

[Данил Самодуров]

Спасибо. Вроде работает. О таком я не знал. А если, все-таки через подзапрос?

[Slava Rozhnev]

Данил Самодуров,

О таком я не знал.

Теперь знаете.
Через позапрос - банально и неинтересно - справитесь сами, а мне лень :)