Subquery instead of distinct and order by in postgres

Question

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.

Farshid Shekari · Accepted Answer · 2022-09-19 16:45:33Z

1

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

answered Sep 19, 2022 at 16:45

Farshid Shekari

2,4394 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Isolated · Accepted Answer · 2022-09-19 16:56:49Z

1

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

edited Sep 19, 2022 at 16:56

answered Sep 19, 2022 at 16:46

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Collectives™ on Stack Overflow

Subquery instead of distinct and order by in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related