CODEWITHSUNDEEP
sqlpostgresqlselectduplicates
Macbernie
Macbernie

Reputation: 1323

SQL return first row of a group

my request:

SELECT this, annonceur, campagne, date FROM campagne ORDER BY id ASC

return:

this    annonceur   campagne    date
A_d20151006174516_rBLA_175030   Bla Bla(001)    2015-10-07 17:51:35.290706
A_d20151006174516_rBLA_175030   Bla Bla(002)    2015-10-07 17:51:35.488242
A_d20151006174516_rBLA_175030   Bla Bla(003)    2015-10-07 17:51:35.530307
A_d20151006174516_rBLA_175030   Bla Bla(004)    2015-10-07 17:51:35.559641

A_d20151008124248_rBLA_124409   Bla Bla(001)    2015-10-09 12:44:13.612378
A_d20151008124248_rBLA_124409   Bla Bla(002)    2015-10-09 12:44:13.708588
A_d20151008124248_rBLA_124409   Bla Bla(003)    2015-10-09 12:44:13.717071
A_d20151008124248_rBLA_124409   Bla Bla(004)    2015-10-09 12:44:13.724947
A_d20151008124248_rBLA_124409   Bla Bla(005)    2015-10-09 12:44:13.731896
A_d20151008124248_rBLA_124409   Bla Bla(006)    2015-10-09 12:44:13.738579

A_d20151011190614_rTest_190714  Test    Test 12102015 - v2(001) 2015-10-12 19:07:30.682369
A_d20151011190614_rTest_190714  Test    Test 12102015 - v2(002) 2015-10-12 19:07:30.744372
A_d20151011190614_rTest_190714  Test    Test 12102015 - v2(003) 2015-10-12 19:07:30.787934

A_d20151013175114_rBLA-VIA-HELLO_175240 Bla via Hello   Bla 14102015(001)   2015-10-14 17:56:23.97971
A_d20151013175114_rBLA-VIA-HELLO_175240 Bla via Hello   Bla 14102015(002)   2015-10-14 17:56:24.183737
A_d20151013175114_rBLA-VIA-HELLO_175240 Bla via Hello   Bla 14102015(003)   2015-10-14 17:56:24.269622
A_d20151013175114_rBLA-VIA-HELLO_175240 Bla via Hello   Bla 14102015(004)   2015-10-14 17:56:24.32696

How can I return only the first row of each "block", like:

this    annonceur   campagne    date
A_d20151006174516_rBLA_175030   Bla Bla(001)    2015-10-07 17:51:35.290706
A_d20151008124248_rBLA_124409   Bla Bla(001)    2015-10-09 12:44:13.612378
A_d20151011190614_rTest_190714  Test    Test 12102015 - v2(001) 2015-10-12 19:07:30.682369
A_d20151013175114_rBLA-VIA-HELLO_175240 Bla via Hello   Bla 14102015(001)   2015-10-14 17:56:23.97971

In a single request ?

Thanks for help !

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In Postgres, the most efficient method is distinct on. I think you can do what you want as:

SELECT DISTINCT ON (this) this, annonceur, campagne, date
FROM campagne
ORDER BY this, id ASC;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT this, annonceur, campagne, date 
FROM (select *, row_number() over(partition by this order by date) as rn
      from campagne) t
where rn = 1

You can use the row_number function.

Upvotes: 1

Related Questions