Search code examples
sqlsql-servert-sql

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

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

Solution

  • 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