MySQL question?

Question

MySQL

Help out. There is a table like:
surname | subject
Ivanov | Physical Education
Ivanov | Biology
Petrov | Physical
Education Sidorov | Physical
Education Sidorov | Biology
Sidorov | Mathematics
Alekseev | Mathematics
Alekseev | Biology
, etc.
Those. one unique surname can correspond to several different items. The table is quite large (about 170,000 rows).
Question: how to select in turn all rows with items containing 3 unique last names.
Those. for example (for the first sample) the result should be:
Ivanov | Physical Education
Ivanov | Biology
Petrov | Physical
Education Sidorov | Physical
Education Sidorov | Biology
Sidorov | Mathematics
The first thought was - a nested query to select unique surnames in it with a limit of n,3 and, then, in an external query, all rows where the surnames match the selected ones. It turned out that LIMIT does not work in nested queries :(
Please tell me how to solve such a problem. If there are options, I'm interested in the best solution in terms of speed. Thank you.
Upd: It means that you need to select 3 unique surnames at a time, starting with n- i.e. if nested queries with limit worked, it would be something like this:
select * from testid where surname in (select distinct surname from testid order by surname limit 0,3)
select * from testid where surname in (select distinct surname from testid order by surname limit 3,3)
select * from testid where surname in (select distinct surname from testid order by surname limit 6,3)

Answer 1

//Unique surname... Where did you find such people?
select * /*не делайте так, указывайте явно поля*/ from testid join (select surname from testid group by surname limit 0,3) as uniqsurnames using (surname)
While I'm pretty vague about what you want, a join with a query is much better than a subquery due to mysql's dumb optimizer.

Answer 2

1) In fresh muscles, the limit in nested ones seems to work.
2) If the muscle is not fresh, you can make a selection in a temporary table, then appeal to it.
3) To be honest, the nested query solution seems overweight anyway.
3) You set the task as “3 unique surnames”, however, in the solution example Sidorov = 3, Petrov = 1, Ivanov = 2 ... and the example with a nested select (n, n + 3) does not mean at all what it is :) You need " no more than 3"?
4)
select * from testid where surname in ( SELECT
surname FROM `testid` WHERE 1
group by surname having count(subject)<=3
)
.

Answer 3

Wouldn't it be appropriate to use a cursor for this task? Or did I misunderstand the condition?