Can I combine SELECT TOP() with the IN operator?

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *