LIMIT by distinct values in PostgreSQL

Question

I have a table of contacts with phone numbers similar to this:

Name    Phone
Alice   11
Alice   33
Bob     22
Bob     44
Charlie 12
Charlie 55

I can't figure out how to query such a table with LIMITing the rows not just by plain count but by distinct names. For example, if I had a magic LIMIT_BY clause, it would work like this:

SELECT * FROM "Contacts" ORDER BY "Phone" LIMIT_BY("Name") 1

Alice 11
Alice 33
-- ^ only the first contact


SELECT * FROM "Contacts" ORDER BY "Phone" LIMIT_BY("Name") 2

Alice   11
Charlie 12
Alice   33
Charlie 55
-- ^ now with Charlie because his phone 12 goes right after 11. Bob isn't here because he's third, beyond the limit

How could I achieve this result?

In other words, select all rows containing top N distinct Names ordered by Phone

Answer 1

I don't think that PostgreSQL provides any particularly efficient way to do this, but for 6 rows it doesn't need to be very efficient. You could do a subquery to compute which people you want to see, then join that subquery back against the full table.

select * from 
"Contacts" join
(select name from "Contacts" group by name order by min(phone) limit 2) as limited 
using (name)

You could put the subquery in an IN-list rather than a JOIN, but that often performs worse.

Answer 2

If you want all names that are in the first n rows, you can use in:

select t.*
from t
where t.name in (select t2.name
                 from t t2
                 order by t2.phone
                 limit 2
                );

If you want the first n names by phone:

select t.*
from t
where t.name in (select t2.name
                 from t t2
                 group by t2.name
                 order by min(t2.phone)
                 limit 2
                );