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?

Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — misorude, Sep 12 '19 at 09:41

score 0 · Answer 1 · answered Sep 12 '19 at 09:27

0

I don't understand if your problem is a Transact-SQL or your code.

In SQL you can limit record with "LIMIT": https://www.w3schools.com/sql/sql_top.asp

In code, you can use a condition IF.

answered Sep 12 '19 at 09:27

Stefano Pascazi

353
1
10

I want use only sql request, without if in php code – Mykhailo K Sep 12 '19 at 09:33
In SQL: SELECT * FROM table LIMIT 0,2 for exract latest 2 result. You can increse a SQL, with where clause. Ex: SELECT * FROM table WHERE user_id = 2 LIMIT 0,2 – Stefano Pascazi Sep 12 '19 at 09:36
I want to select no more than two records for each user – Mykhailo K Sep 12 '19 at 09:39

GMB · Accepted Answer · 2019-09-13T22:32:55.720

0

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

edited Sep 13 '19 at 22:32

answered Sep 12 '19 at 09:58

GMB

195,563
23
62
110

i am using old mysql, without partition by – Mykhailo K Sep 12 '19 at 10:11
@MykhailoK: then you might want to have a look at the link that was flagged as a duplicate of your question, which provides solutions for older versions of MySQL. – GMB Sep 12 '19 at 10:31
doesnot work form me, i can understand this sql query – Mykhailo K Sep 12 '19 at 16:39
@MykhailoK: I updated my answer with a query for MySQL < 8.0 – GMB Sep 13 '19 at 22:33

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

2 Answers2