How we can get the data from the table by limiting the number of identical columns MySql

Question

Yesterday I tried to retrieve data from my db table using 'user_id' as a criterion to limit the amount of data per user.

I tried to get data from table https://prnt.sc/p53zhp in format like this https://prnt.sc/p541wk and limit the number of output records for user_id where limit will be 2 (count(user_id) <= 2), but i don't understand how to do that. What kind of sql request can i use to get this data?

Answer 1

Assuming that your RDBMS, here is a solution yo select only the top 2 records per user. You can use ROW_NUMBER() in a subquery to rank records by id within groups of records having the same user_id, and the filter out unerelevant records in the outer query, like:

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY id)
    FROM mytable
) x WHERE rn <= 2

---

On earlier versions of MySQL, you could use self-LEFT JOIN the table and use GROUP BY and HAVING COUNT(...) < 2 to limit the results to first two records per group:

SELECT 
    t.id, 
    t.user_id, 
    t.vip, 
    t.title,
    t.description,
    t.data 
FROM mytable t
LEFT JOIN mytable t1 ON t1.user_id = t.user_id AND t1.id > t.id
GROUP BY
    t.id, 
    t.user_id, 
    t.vip, 
    t.title,
    t.description,
    t.data 
HAVING COUNT(t1.id) < 2