MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE services (service_id INT, service_name VARCHAR(255), service_limit INT);
INSERT INTO services VALUES
(5 , 'Sample Service'      , 5),
(8 , 'Other tool'          , 2);
SELECT * FROM services;

Records: 2  Duplicates: 0  Warnings: 0




service_id
service_name
service_limit


5
Sample Service
5

8
Other tool
2


CREATE TABLE orders (id INT, service_id INT, status INT);
INSERT INTO orders VALUES
(1  ,  5     , 0),
(2  ,  5     , 0),
(3  ,  5     , 0),
(4  ,  8     , 0),
(5  ,  8     , 0),
(6  ,  8     , 0),
(7  ,  8     , 0);
SELECT * FROM orders;

Records: 7  Duplicates: 0  Warnings: 0




id
service_id
status


1
5
0

2
5
0

3
5
0

4
8
0

5
8
0

6
8
0

7
8
0


SELECT *
FROM services
JOIN (
    SELECT *,
           @row_number := CASE WHEN service_id = @service_id
                               THEN @row_number + 1
                               ELSE 1
                               END rownumber,
           @service_id := service_id
    FROM orders
    CROSS JOIN (SELECT @service_id := 0, @row_number := 0) init_vars
    ORDER BY service_id, id DESC
    ) enumerated_orders USING (service_id)
WHERE enumerated_orders.rownumber <= services.service_limit


service_id
service_name
service_limit
id
status
@service_id := 0
@row_number := 0
rownumber
@service_id := service_id


5
Sample Service
5
3
0
0
0
1
5

5
Sample Service
5
2
0
0
0
2
5

5
Sample Service
5
1
0
0
0
3
5

8
Other tool
2
7
0
0
0
1
8

8
Other tool
2
6
0
0
0
2
8

service_id	service_name	service_limit	id	rownumber	@service_id := service_id
5	Sample Service	5	3	1	5
5	Sample Service	5	2	2	5
5	Sample Service	5	1	3	5
8	Other tool	2	7	1	8
8	Other tool	2	6	2	8