Mysql - Select a certain number of rows for every WHERE IN condition value-CodePudding

I want to select maximum 2 rows for each value in condition

SELECT * FROM files WHERE department IN (2,3,4);

Here is table I have

department	file
1	Innovation Arch
1	Strat Security
1	Inspire Fitness Co
1	Candor Corp
2	Cogent Data
2	Epic Adventure Inc
2	Sanguine Skincare
2	Vortex Solar
3	Admire Arts
3	Bravura Inc
3	Bonefete Fun
3	Moxie Marketing
3	Zeal Wheels
4	Obelus Concepts

And this is what I would like to achieve

department	file
2	Cogent Data
2	Epic Adventure Inc
3	Moxie Marketing
3	Zeal Wheels
4	Obelus Concepts

In case if you need:

CREATE TABLE files (department INT, file VARCHAR(20));
INSERT INTO files (department, file) VALUES
(1, "Innovation Arch"),(1, "Strat Security"),(1, "Inspire Fitness Co"),(1, "Candor Corp"),
(2, "Cogent Data"),(2, "Epic Adventure Inc"),(2, "Sanguine Skincare"),(2, "Vortex Solar"),
(3, "Admire Arts"),(3, "Bravura Inc"),(3, "Bonefete Fun"),(3, "Moxie Marketing"),(3, "Zeal Wheels"),
(4, "Obelus Concepts");

CodePudding user response：

If "the rows can be anything" you could simply apply a row_number window and filter:

select department, file from (
  select *, row_number() over(partition by department order by department) rn
  from files
)t
where rn <= 2 and department in (2,3,4);

Demo fiddle

You can also order by file to choose two rows alphabetically.