CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
sealabr
sealabr

Reputation: 1672

Postgresql - Return (N) rows for each ID

I have a table like this

contact_id |  phone_number 
         1 |  55551002
         1 |  55551003
         1 |  55551000
         2 |  55552001
         2 |  55552008
         2 |  55552003
         2 |  55552007
         3 |  55553001
         3 |  55553002
         3 |  55553009
         3 |  55553004
         4 |  55554000

I want to return only 3 numbers of each contact_id, order by phone_number, like this:

contact_id | phone_number
         1 |  55551000
         1 |  55551002
         1 |  55551003
         2 |  55552001
         2 |  55552003
         2 |  55552007
         3 |  55553001
         3 |  55553002
         3 |  55553004
         4 |  55554000

please need be an optimized query.

My Query

SELECT a.cod_cliente, count(a.telefone) as qtd
FROM crm.contatos a
  LEFT JOIN (
    SELECT *
    FROM crm.contatos b
    LIMIT 3
  ) AS sub_contatos ON sub_contatos.cod_contato = a.cod_cliente
group by a.cod_cliente;

Upvotes: 1

Views: 46

Answers (1)

user330315
user330315

Reputation:

This type of query can easily be solved using window functions:

select contact_id, phone_number
from (
  select contact_id, phone_number, 
         row_Number() over (partition by contact_id order by phone_number) as rn
  from crm.contatos
) t
where rn <= 3
order by contact_id, phone_number;

Upvotes: 3

Related Questions