Select from table rows that match all items in a where in list

Question

I have a 5 field table, and I want to make a qry like this:

SELECT *
FROM dbo.table
WHERE somefield in (90,120,30,90)

The problem is that I have several 90, 120 and 30 values in the rows of the table, but I want to return only first 4 rows, that match the criteria.

Is there any easy way to do this? I'm on SQL server 2008.

CREATE TABLE ForgeRock
    ([id] int, [somefield] int)
;

INSERT INTO ForgeRock
    ([id], [somefield])
VALUES
    (1, 90),
    (2, 90),
    (3, 120),
    (4, 30),
    (5, 30),
    (6, 90),
    (7, 10),
    (8, 20),
    (9, 90),
    (10, 30),
    (11, 20)
;

Fidle with data and query. Expected results would be 90,120,20,90 and their respected ids.

Sample data and expected results will help us help you. Also, SQL Server 2008 has been completely unsupported for 2 years (almost to the day); you should *really* be looking at upgrade paths by now. — Thom A, Jul 02 '21 at 14:02
@user1228825 a fiddle should compliment a question/answer but not be required. Put the DDL and DML in your question, using the [edit] feature,, not a fiddle in the comments. — Thom A, Jul 02 '21 at 14:19
*"Put the DDL and DML in your question"* @user1228825 *not* **just** the hyperlink to the fiddle... — Thom A, Jul 02 '21 at 14:23
For me, as well, the link in your question is broken, so it *really* isn't helpful. Again, sample data and expected results, will help us help you. — Thom A, Jul 02 '21 at 14:27
Tables in SQL is an unordered set of rows, DBMS doesn't know, which *first 4 rows* you want to match. And how do you get this list of values? It should be converter to set of rows and joined to the table, but in provided format it cannot be processed this way — astentx, Jul 02 '21 at 16:43
@user1228825 I got you requirement, but it depends on the input of filter criteria. If you are able to rewrite the query and change the type of this input to make it tabular, then I can try to help — astentx, Jul 02 '21 at 16:53
query can be changed of course... cause the example that I've posted doesn't work... can you please show your idea ? — user1228825, Jul 02 '21 at 16:55
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 02 '21 at 17:09

score 0 · Accepted Answer · answered Jul 02 '21 at 17:19

You a filtering by list, and in just checks it for membership of every input value. So it doesnt't matter what is the ordinal position of the value in list, it's intended for filtering only. To distinguish two different instances of the same value you need to turn this list into table or do some other transformation that holds the order information.

One way can be a table value constructor:

with flt as (
  select
    val,
    row_number() over(partition by val order by val) as rn /*To distinguish instances of the value*/
  from(values (90),(120),(30),(90)) as t(val)
)
, base as (
  select
    f.*,
    row_number() over(partition by somefield order by id) as rn
  from ForgeRock as f
  where somefield in (select flt.val from flt) /*To restrict input before row number*/
)
select b.*
from base as b
  join flt
    /*Match value AND repetition*/
    on b.somefield = flt.val
      and b.rn = flt.rn

id somefield rn

4 30 1

1 90 1

2 90 2

3 120 1

For modern versions there's also openjson possibility, if you receive this list from the outside:

with flt as (
  select
    [value] as val,
    row_number() over(partition by [value] order by [key]) as rn /*To distinguish instances of the value*/
  from openjson('[90, 120, 30, 90]') as t
)
...
the same code

Select from table rows that match all items in a where in list

1 Answers1