PostgreSQL - Get first and last record of relation with one query

Question

I have the following schema in my database:

   CREATE TABLE users (
        id integer NOT NULL
    );   

   CREATE TABLE survey_results (
        id integer NOT NULL,
        name text,
        user_id integer,
        created_at timestamp without time zone
    );

    INSERT INTO users (id)
        VALUES (1);    

    INSERT INTO survey_results (id, name, user_id, created_at)
        VALUES (1, 'TEST 1', 1, now());

    INSERT INTO survey_results (id, name, user_id, created_at)
        VALUES (2, 'TEST 2', 1, now());

    INSERT INTO survey_results (id, name, user_id, created_at)
        VALUES (3, 'TEST 3', 1, now());

Now I want to get name of first and last user survey_result in one query, so result should look like this

id   first   last

1    TEST1  TEST2

How can I do this in PostgreSQL?

Here is sqlfiddle with this schema: https://www.db-fiddle.com/f/aC2DrJXqmJc1ZLkdEjLnht/0

You mean first=TEST1 and last=TEST3, right?

Asfand Qazi
– Asfand Qazi

2021-11-11 09:55:29 +00:00
Commented Nov 11, 2021 at 9:55 — Asfand Qazi
– Asfand Qazi, Commented Nov 11, 2021 at 9:55

user330315 · Accepted Answer · 2018-03-29 10:55:57Z

1

Something like this:

select m.user_id, 
       f.name as first_survey, 
       f.created_at as first_survey_created,
       l.name as last_survey, 
       l.created_at last_survey_created
from (
    select user_id, 
           min(created_at) as first_created, 
           max(created_at) as last_created
    from survey_results 
    group by user_id
) m
  join survey_results f on f.user_id = m.user_id and f.created_at = m.first_created
  join survey_results l on l.user_id = m.user_id and l.created_at = m.last_created;

This will not work however, if the first and last survey have the same created_at value

answered Mar 29, 2018 at 10:55

user330315

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

Comments

Loofer · Accepted Answer · 2018-03-29 10:50:41Z

1

It ain't pretty!

select 
u.id as user_id,
(select name from survey_results where user_id = u.id order by created_at asc limit 1) as first,
(select name from survey_results where user_id = u.id order by created_at desc limit 1) as last
from users u

I made it a bit more pretty (using joins rather than correlated subquerys) and this allows your extra functionality.

select 
u.id
,min.name as minName
,min.created_at as minDate
,max.name as maxName
,max.created_at as maxDate
from users u 
join (select * from survey_results  order by created_at asc limit 1  ) as min on min.user_id = u.id
join (select * from survey_results  order by created_at desc limit 1  ) as max on min.user_id = u.id

edited Mar 29, 2018 at 10:50

answered Mar 29, 2018 at 10:36

Loofer

7,0239 gold badges67 silver badges107 bronze badges

1 Comment

Mateusz Urbański Over a year ago

Thanks this works but is there any way to show created_at of first and last survey_result in that query?

Collectives™ on Stack Overflow

PostgreSQL - Get first and last record of relation with one query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related