Postgres query with limit that selects all records with similar identifier

1
votes

I have a table that looks something like this:

customer_id data
1 123
1 456
2 789
2 101
2 121
2 123
3 123
4 456

What I would like to do is perform a SELECT combined with a LIMIT X to get X number of records as well as any other records that have the same customer_id

Example query: SELECT customer_id, data FROM table ORDER BY customer_id LIMIT 3; This query returns:

customer_id data
1 123
1 456
2 789

I'd like a query that will look at the last customer_id value and return all remaining records that match beyond the LIMIT specified. Is it possible to do this in a single operation?

Desired output:

customer_id data
1 123
1 456
2 789
2 101
2 121
2 123
2
sqlpostgresql

2 Answers

0
votes

In Postgres 13 can use with ties:

select t.*
from t
order by customer_id
fetch first 3 rows with ties;

In earlier versions you can use in:

select t.*
from t
where t.customer_id in (select t2.customer_id
                        from t t2
                        order by t2.customer_id
                        limit 3
                       );
0
votes

You can use corelated subquery with count as follows:

Select t.*
  From t
 Where 3 >= (select count(distinct customer_id)
               From t tt 
              where t.customer_id >= tt.customer_id)