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

Postgresql – Sql joins with the last entry

I’ve have an sql database similar to the following:

Note: there is a missing column personId in event.

There are tables: Person that has many Events. Travel, playsBasquetBall and playsFooball are types of event.

How can I make a query that retunrs, for each person its current place (last travel event) and type of the last match (basquetball or football)?
Is theres somehow that I can do to skip the subqueries like the following (a part than denormalizing tables)?

SELECT person.*, 
       ( SELECT place 
         FROM Travel 
         JOIN Event event ON place.id = event.id 
                          AND event.personId = person.id
       )

Example data:

Person(1)
Person(2)
Event(1, 20001001)
Travel(1, "Barcelona")
Event(2, 20011001)
Travel(2, "Paris")
Event(3, 2041001)
Travel(3, "Girona")
Event(4, 20001001)
Travel(4, "Barcelona")

Example response:

1, 4, Barcelona
2, null, null
Tags:

2

Answers


  1. 0

    Assuming the table "Event" have a column named "personId"

    This can be done using Multiple LEFT JOINs :

    SELECT p.id, e2.id as event_id, t.place
FROM Person p
LEFT JOIN (
  SELECT personId, MAX(date) AS latest_date
  FROM Event
  GROUP BY personId
) e ON e.personId = p.id
LEFT JOIN Event e2 ON e.personId = e2.personId
                   AND e.latest_date = e2.date
LEFT JOIN travel t on t.eventId = e2.id;

    For this simple data :

    create table Person (
  id int
);

insert into Person values
(1),(2);

create table Event (
  id int,
  personId int,
  date int
);

insert into Event values (1, 1, 2001001),(2, 1, 2001101),(3, 1, 2001201),(4, 1, 2001301);

create table Travel (
  eventId int,
  place varchar(20)
);

insert into Travel values (1, 'Barcelona'), (2, 'Paris'), (3, 'Girona'), (4, 'Barcelona');

    Results :

    id  event_id    place
1   4           Barcelona
2   null        null

    Demo here

    Login or Signup to reply.
  2. 0

    I believe you could use ’distinct on’ in a sub select left join.

    Something like this

    select person.id
       _.place
from person
left join (
    select distinct on (event.person_id),
           event.person_id,
           travel.place
    from event
    join travel on travel.event_id = event.id
    order by person_id, date desc
) _ on _.person_id = person.id

    Would need to do similar for the sport event tables maybe using a ’union all’

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

Back To Top
Search