Limit to N number with one-to-many relationship

Question

I'm looking for a way to return 100 unique events which are correlated with several records from another table.

Normally I would use TOP to return a certain number of records, but I'm dealing with a one-to-many join.

For example, in the included image, I'm looking to return the top three fruits and all of their records as highlighted (or just three fruits in general--not picky on order), but I am not aware of a way to do this in SQL.

Answer 1

You could use dense_rank():

select *
from (
    select t.*, dense_rank() over(order by fruit) rn
    from mytable t
) t
where rn <= 3

This gives you all rows for the "first" three fruits, as defined by their alphabetical order.