7

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.

  • You need in 50 RANDOM records for each COLUMN value? or TOP 50 while sorting by some expression? If last - what if 50th and 51th records have the same exopression value? – Akina May 5 at 11:53
  • @Akina Sorting is required. In the context I am using this, multiple rows won't match to the same expression value – Abs May 5 at 12:03
10

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.

6

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
  • I did not understand what a,b,c are doing here . I was wondering if you could explain a little bit more please . thanks in advance – Pantea Tourang May 7 at 14:23
  • 1
    @Pantea a, b, c are fictional column names that you want to see in results (and d is the fictional column used for ordering the results). It is always better to name the columns you want in the results instead of SELECT * FROM... – Salman A May 7 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.