Question 114564 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Subquery instead of distinct and order by in postgres

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search