Can I combine SELECT TOP() with the IN operator?
One method is with CROSS APPLY
:
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 TOP
, ORDER BY
is needed and the ordered by columns should be unique. An index with a composite key on ID
and 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