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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0