# Is there way to limit number of rows for each element in a IN clause?

I am trying to write a query that looks like this:

`Select * from table1 where field1 = 4 and field2 in (2,3,4,5);`

Let’s say I want 25 rows for each element in my field2 list (100 rows all together), how can I achieve this?

Contents

### Method 1

If you add a ROW_NUMBER to your query with field2 as Ppartition, you can select the number of rows you need per field.

you have to add a `ORDER BY` to the window function tio get the correct rows, but i don’t know enough about your table

```SELECT *
FROM
(Select *, ROW_NUMBER() OVER(PARTITION BY fild2 ) rn from table1 where field1 = 4 and field2 in (2,3,4,5)) t2
WHERE rn <= 4```

and here is a version for mysql 5.7

`CREATE TABLE table1 ( field1 int ,field2 int)`
`INSERT INTO table1 VALUES(4,2),(4,2),(4,2),(4,2),(4,2),(4,3),(4,3),(4,3),(4,3),(4,4)`
```SELECT * FROM
(SELECT
field1,
IF (@field2 = field2 ,@row_number :=  @row_number + 1,@row_number := 1) AS rn,
@field2 := field2 field2
FROM
(SELECT * FROM table1 where field1 = 4 and field2 in (2,3,4,5) ORDER BY field2) t1,
(SELECT @field2:=0,@row_number:=0) as t ) t2
WHERE rn <= 4```
```field1 | rn | field2
-----: | -: | -----:
4 |  1 |      2
4 |  2 |      2
4 |  3 |      2
4 |  4 |      2
4 |  1 |      3
4 |  2 |      3
4 |  3 |      3
4 |  4 |      3
4 |  1 |      4
```

db<>fiddle here

### Method 2

If you are concern by this, you are having an architecture problem. The limit exists but you have to avoid it even the half, in any case the wrong consumptions of your own resources.

Several solutions to this, but the normal situation is make a temporal (USP) or definitive transactional table, put the data in them, select/join the data and drop temporal table / truncate definitive table, after use.