Clever Geek Handbook

How to select 10 rows for each column value in an "IN" statement (T-SQL)

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

Here's an example request:

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

I only want 10 people named Rachel and 10 with Adam and 10 with Terry. There are 1000 with that name in the database. How can I do this without using union?

This is just an example of SQL. The one I'm working with has millions of lines

+3


source to share


4 answers


Here's the 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
+4


source


Here is one way to use the operator Apply

andTable 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 the following index

will help speed up the query execution

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


source


One parameter ROW_NUMBER()

:

SELECT student_id, student_name, student_phnumber
FROM (
     SELECT student_id
           ,student_name
           ,student_phnumber
           , ROW_NUMBER() OVER (PARTITION BY student_name ORDER BY student_name) RN
     FROM student
     WHERE student_name in ( 'Rachel','Adam','Terry')
     ) A
WHERE RN < 11

@scsimon Kill me. I'll leave this just to show the built-in way of writing it.

+2


source


select * from ( Select student_id ,student_name ,student_phnumber ,row_number() over (partition by student_name order by student_id) rn from student where group by student_name ) where student name in ( 'Rachel','Adam','Terry')) and rn <11;

-1


source






More articles:


All Articles