Subquery instead of distinct and order by in postgres-CodePudding

I have two tables: Company, Event.

Company
{
id,
name
}

Event
{
id,
id_company,
date,
name
}

It's one to many relation. I want to have results of companies (every company only once) with the latest event. I'm using postgres. I have this query:

select distinct on (COM.id)
COM.id,
COM.name,
EVT.date,
EVT.name
FROM Company COM
LEFT JOIN Event EVT on EVT.id_company = COM.id
ORDER BY COM.id, EVT.date DESC

It looks good but I'm wondering if I could have the same result using subqueries or something else instead of distinct and order by date of the event.

CodePudding user response：

You can use row_number and then select row number 1, like below:

select  COM.id,
           COM.name,
           EVT.date,
          EVT.name
    FROM Company COM
    LEFT JOIN (select EVT.*, row_number() over(partition by id_company order by EVT.date DESC) rnk from  Event EVT ) EVT on EVT.id_company = COM.id and rnk=1

CodePudding user response：

You could use rank() to achieve your results using a subquery or CTE such as this one.

  with event_rank as (
    select id_company, date, name, 
    rank() over (partition by id_company order by date desc) as e_rank
    from event
   )
  select c.id, c.name, er.date, er.name
  from company c
  left join event_rank er
    on c.id = er.id_company
  where er.e_rank = 1 
     or er.e_rank is null --to get the companies who don't have an event

View on DB Fiddle