Postgres - Select specific rows for each ID

Question

I have the following scheme:

id | name | price | site_id | agency

1 | NAME | PRICE | 1 | AGENCY1
2 | NAME | PRICE | 1 | AGENCY1
3 | NAME | PRICE | 1 | AGENCY2
4 | NAME | PRICE | 1 | AGENCY2
5 | NAME | PRICE | 2 | AGENCY1
6 | NAME | PRICE | 2 | AGENCY1
7 | NAME | PRICE | 2 | AGENCY1

I want to get the first row with a unique agency for each site_id.

For example, the query result for the above scheme is expected to be:

1 | NAME | PRICE | 1 | AGENCY1
3 | NAME | PRICE | 1 | AGENCY2
5 | NAME | PRICE | 2 | AGENCY1

I have tried to look for solutions with DISTINCT but couldn't figure it out.

Bergi · Accepted Answer · 2019-11-30 12:41:10Z

3

In Postgres, you should use distinct on:

select distinct on (site_id, agency) t.*
from t
order by site_id, agency, id;

Not only is this the most concise method, but it usually has the best performance of possible methods. You want an index on (site_id, agency, id) for optimal performance.

edited Nov 30, 2019 at 12:41

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

answered Nov 30, 2019 at 12:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

forpas · Accepted Answer · 2019-11-30 12:32:58Z

1

With row_number() window function:

select t.id, t.name, t.price, t.site_id, t.agency
from (
  select *, row_number() over (partition by site_id, agency order by id) rn
  from tablename
) t
where t.rn = 1

See the demo.
Results:

| id  | name | price | site_id | agency  |
| --- | ---- | ----- | ------- | ------- |
| 1   | NAME | PRICE | 1       | AGENCY1 |
| 3   | NAME | PRICE | 1       | AGENCY2 |
| 5   | NAME | PRICE | 2       | AGENCY1 |

answered Nov 30, 2019 at 12:32

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Postgres - Select specific rows for each ID

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related