How to limit 4 records for every record selected in mysql?

by wilmer.lemke , in category: MySQL , a month ago

How to limit 4 records for every record selected in mysql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by gilbert , a month ago

@wilmer.lemke 

You can limit the number of records returned for each selected record by using a subquery in MySQL. Here is an example query that limits the result to 4 records for each selected record:

1
2
3
4
5
6
7
8
9
SELECT *
FROM (
    SELECT *,
           @row_number := CASE WHEN @group = group_id THEN @row_number + 1 ELSE 1 END AS rn,
           @group := group_id
    FROM your_table
    ORDER BY group_id, record_id
) AS subquery
WHERE rn <= 4;


In this query:

  • your_table is the table from which you are selecting records.
  • group_id is the column that represents the grouping of records.
  • record_id is a unique identifier for each record.
  • 4 is the limit for how many records you want to return for each selected record.


This query uses variables to assign a row number to each record within a group, and then filters out any records with a row number greater than 4. This will effectively limit the result to 4 records for each selected record.