sealabr sealabr - 1 month ago 5
SQL Question

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;

Answer

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;
Comments