MySQL limitation with WHERE IN clause

Question

This is slightly different from the solutions that I have found so far. I have a relationship between users and companies (users have a company_id column). I need to create a request that will receive the first 5 users in the company. The first 3 users are listed below:

SELECT * FROM users WHERE company_id IN (1, 2, 3, ...) LIMIT 3;

Instead of the first three users per company. Right now I am browsing companies in PHP and running a query that captures the user by company id with LIMIT 3, but it is possible to have 100 companies, so this doesn't work at all. Is there a way to get the first "n" users in the list of companies if I have their identifiers?

Answer 1

MySQL :

SELECT u.*
FROM (SELECT u.*,
             (@rn := if(@c = company_id, @rn + 1,
                        if(@c := company_id, 1, 1)
                       )
             ) as seqnum
      FROM users u CROSS JOIN
           (SELECT @c := -1, @rn := 0) params
      WHERE u.company_id IN (1, 2, 3, ...)
      ORDER BY u.company_id
     ) u
WHERE seqnum <= 3;

. SQL . , " ", . , ORDER BY.

. MySQL SELECT. , , - , . .