Сортировка по связующей таблице?

Question

[Алексей]

Всем привет!

Есть такие таблицы
companies
-id
-name

teams
-id
-name
-company_id

polls
-id
-team_id
-created_at

Вопрос такой, как получить все записи из таблицы companies отсортированные по polls.created_at без повторений.
Если у company есть несколько team, а у team несколько poll, должна возвращаться запись с самым последним poll

Пример вывода:

company.name | poll.created_at
Company 1 | 2019-01-01
Company 2 | 2019-02-02

Я пробовал делать через join, но как и предполагалось, выдаются дубликаты по company.name

Answer 1

[Руслан .]

А почему через join не вышло?
Можно так попробовать:

SELECT companies.name, max(polls.created_at)
  FROM polls
  JOIN teams
    ON teams.id = polls.team_id
  JOIN companies
    ON companies.id = teams.company_id
 GROUP BY companies.name
 ORDER BY 2

Comments

[Алексей]

Спасибо, это работает! А как быть с тем, если для company нет poll? Хотелось бы чтобы в этом случает в poll.created_at было null, а по вашему решению просто не возвращается данная company

[Константин Цветков]

Алексей, см. Константин Цветков

[Руслан .]

Алексей, если нет poll, то да - будет пустой датасет.

Чтобы было как вы хотите нужно перевернуть запрос вот так:

SELECT companies.name, max(polls.created_at)
   FROM companies
   JOIN teams
     ON teams.company_id = companies.id
   LEFT
   JOIN polls
     ON polls.team_id = teams.id
  GROUP BY companies.name
  ORDER BY 2

Но правда, это может быть не так эффективно, как в первом варианте из-за того, что в teams может не быть индекса по company_id, а в polls по team_id

Answer 2

[Константин Цветков]

все записи из таблицы companies

SELECT companies.name, MAX(polls.created_at)
  FROM polls 
    INNER JOIN teams ON polls.team_id = teams.id
    RIGHT OUTER JOIN companies ON teams.company_id = companies.id
  GROUP BY companies.Name

Comments

[Алексей]

Спасибо! Чуть подправил с сортировкой

SELECT company.name, MAX(poll.created_at) as last
FROM poll
         INNER JOIN team ON poll.team_id = team.id
         RIGHT OUTER JOIN company ON team.company_id = company.id
GROUP BY company.name
order by last DESC

Answer 3

[TheRonCronix]

select company_name, poll_created_at
from
(
  select company.name as company_name, poll.created_at as poll_created_at,
    row_number() over (partition by company.name order by poll.created_at desc) as rn
  from ...
)
where rn = 1;