CODEWITHSUNDEEP
mysqlsqlselect
Soheil
Soheil

Reputation: 627

MySQL-select 10 rows with limit on categories column

I have a table in my database which has 3 columns: (id, business_id, name). I need to write a query which selects 10 rows from table which have id greater than a specific value and the point is that not more than 5 rows must be selected for each business_id. how to include this criteria in the query?

so for example if we have these rows in table:

1   A   JAD
2   A   LPO
3   A   LMN
4   A   ABC
5   A   QWE
6   A   WER
7   B   TYU
8   B   POI
9   B   AQZ
10  B   UYT
11  C   CDE
12  C   XYZ

the desired result is (for id>0):

1   A   JAD
2   A   LPO
3   A   LMN
4   A   ABC
5   A   QWE
7   B   TYU
8   B   POI
9   B   AQZ
10  B   UYT
11  C   CDE

Upvotes: 0

Views: 1354

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In older versions of MySQL, you can use:

select t.*
from t
where id > @id and
      id < any (select t2.id
                from t t2
                where t2.business_id = t.business_id
                order by id asc
                limit 1 offset 4
               )
limit 10;

The any is to handle the case where a business has fewer than four rows.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

If you are using MySQL 8+, then ROW_NUMBER can be used here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY business_id ORDER BY id) rn
    FROM yourTable
)

SELECT id, business_id, name
FROM cte
WHERE rn <= 5;

Upvotes: 1

Related Questions