CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

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

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

Upvotes: 1

Views: 3504

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

Loofer
Loofer

Reputation: 6965

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

Upvotes: 1

Related Questions