Postgres: Need to get the first row with minimum value for (datecreated)

Question

I need help.. I have a table below.. I need to get the entire row with minimum value for the datecreated column

  id      group    users       datecreated
  ===========================================
 *39583 | group1 | user1 | 7/6/2015 23:28
  39583 | group1 | user1 | 7/6/2015 23:37
  39583 | group1 | user2 | 7/7/2015 15:27
  39583 | group1 | user2 | 7/7/2015 19:39
  39583 | group1 | user3 | 7/7/2015 22:17
  39583 | group1 | user4 | 7/8/2015 19:18
  39583 | group1 | user3 | 7/9/2015 2:35
  39583 | group1 | user5 | 7/9/2015 14:19
 *39123 | group1 | user5 | 7/5/2015 14:19
  39123 | group1 | user1 | 7/5/2015 23:28
  39123 | group1 | user1 | 7/5/2015 23:37
  39123 | group1 | user2 | 7/5/2015 15:27
  39123 | group1 | user2 | 7/6/2015 19:39
  39123 | group1 | user3 | 7/6/2015 22:17
  39123 | group1 | user4 | 7/6/2015 19:18
  39123 | group1 | user3 | 7/7/2015 2:35
  39123 | group1 | user5 | 7/7/2015 14:19
  39123 | group1 | user5 | 7/7/2015 14:19

I want to get the following rows... in postgres

id    group    users       datecreated
===========================================
39583   | group1 | user1 | 7/6/2015 23:28
39123   | group1 | user5 | 7/5/2015 14:19

What logic did you use to get only those 2 rows? Why rows with user2..4 are excluded? — Christian
– Christian, Commented Sep 15, 2015 at 18:44

Vamsi Prabhala · Accepted Answer · 2015-09-15 12:31:35Z

1

You can use row_number to get the desired result.

select id, group, users, datecreated from
(
select *, row_number() over(partition by id order by datecreated) as rn
from tablename
) t 
where t.rn = 1;

answered Sep 15, 2015 at 12:31

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

Comments

Rahul · Accepted Answer · 2015-09-15 12:33:39Z

0

You can do a GROUP BY and perform a JOIN with the main table like

select t.id,      
t.group,    
t.users,       
t.datecreated
from table1 t join (
select min(datecreated) as mindatecreated, id
from table1
group by id) xx 
on t.id = xx.id and t.datecreated = xx.mindatecreated;

answered Sep 15, 2015 at 12:33

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

user330315 · Accepted Answer · 2015-09-15 13:15:25Z

0

Using Postgres' distinct on() is usually faster than a solution using window function (and both are usually faster than a solution with a sub-select)

select distinct on (id) id, "group", users, datecreated
from the_table
order by id, datecreated;

note that group is a reserved keyword and cannot be used as a column name without quoting.

answered Sep 15, 2015 at 13:15

user330315

Collectives™ on Stack Overflow

Postgres: Need to get the first row with minimum value for (datecreated)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related