How to select 10 rows for a each column value in the 'IN' statement (T-SQL)

Question

How to pull 10 records of data for each listed in the IN statement in T-SQL?

Here is the query example:

SELECT 
    student_id, student_name, student_phnumber
FROM 
    student
WHERE 
    student_name IN ('Rachel', 'Adam', 'Terry')

I want only 10 people with name Rachel and 10 with Adam and 10 with Terry. In the database, there are 1000's with that name. How do I do this without using union?

This is just an example SQL. The one I am working with it has millions of rows

Answer 1

Here's a way with a window function.

with cte as(
   Select student_id
      ,student_name
      ,student_phnumber
      ,row_number() over (partition by student_name order by student_id) rn
from student
where student_name in ( 'Rachel','Adam','Terry'))

select * from cte where rn < 11

Answer 2

Here is one way using Apply operator and Table valued constructor

SELECT tc.student_name, 
       oa.* 
FROM   (VALUES ('Rachel'),('Adam'),('Terry'))tc (student_name) 
       OUTER apply (SELECT TOP 10 student_id, 
                                  student_name, 
                                  student_phnumber 
                    FROM   student a 
                    WHERE  a.student_name = tc.student_name) oa 

creating following index will help the query to run faster

create nonclustered index nix_student 
           on student (student_name) include (student_id,student_phnumber )