Как правильно составить сложный запрос MySQL?

Question

[svirepui]

Мне нужно вывести топ 5 названий комнат(таблица rooms) с помощью одного SQL запроса. Топ измеряется количеством уникальных пользователей(из таблицы publications), оставивших хоть одну публикацию в комнате.

Структура таблиц:

1. publications
------------------------------------------------------
id | author_id | name_room | msg | date_ |
------------------------------------------------------
2. rooms
-------------------------------------------------
id | name | author_id | date_ | title | descr |
-------------------------------------------------

Пытался сделать так:

SELECT name, (SELECT COUNT(DISTINCT author_id) FROM publications WHERE 
name_room = rooms_.name) AS count FROM rooms_ ORDER BY count DESC LIMIT 5

и так :

SELECT name_room, COUNT(DISTINCT author_id) AS count FROM publications ORDER BY count desc limit 5

Но данные способы не работают. Как можно это реализовать?

Comments

[MrTimon]

Хорошо бы иметь структуру таблиц. Тогда можно что-то и подсказать.

Answer 1

[longclaps]

SELECT
  count(name_room) AS rank,
  name_room
FROM (SELECT DISTINCT
        author_id,
        name_room
      FROM publications) AS T
GROUP BY name_room
ORDER BY rank DESC
LIMIT 5;

Answer 2

[MrTimon]

Если следовать вашей структуре то тогда так:

SELECT name_room, COUNT(*) as count_author FROM (SELECT name_room, author_id FROM  publications  GROUP BY name_room, author_id) GROUP BY count_author DESC LIMIT 5

не проверял но должно работать. Один нюанс если комнат в которых сделали публикацию будет меньше 5, то тогда меньше назв и выведутся. Этого можно избежать если сделать JOIN с таблицей rooms но это еще более усложнит запрос

Comments

[svirepui]

Не работает. Ошибка - Every derived table must have its own alias.
По поводу названий так и должно быть.

[MrTimon]

svirepui: Ну допишите после запроса в FROM секции: FROM (...) as p

[svirepui]

MrTimon: теперь другая ошибка

Can't group on 'count_author'.

[MrTimon]

svirepui: блин, опечатка. Там должно быть author_id