I was wondering if there is a way for a
SELECT query to be made on each matched value found within IN.
Consider that the table below contains 100 records for each of the values included referenced by the
INoperator, I’d like only 50 to be returned for each match.
SELECT TOP (50) COLUMN FROM TABLE WHERE COLUMN IN (1,2,3)
Is there an efficient way I can do this?
The only way I could think of is to execute the query for each value within
IN which would be way too slow considering the actual query retrieves many column from various tables.
One method is with
SELECT top_50.ColumnName FROM (VALUES(1),(2),(3)) AS id_list(ID) CROSS APPLY(SELECT TOP(50) t.ColumnName FROM TableName AS t WHERE t.ID = id_list.ID ORDER BY t.ColumnName) AS top_50;
To avoid returning random rows with
ORDER BY is needed and the ordered by columns should be unique. An index with a composite key on
ColumnName will be useful in this example query for efficiency.
You can use
ROW_NUMBER() for this:
SELECT a, b, c FROM ( SELECT a, b, c, ROW_NUMBER() OVER (PARTITION BY match_column ORDER BY d) AS rn FROM t WHERE match_column IN (1, 2, 3) ) AS x WHERE rn <= 50