CODEWITHSUNDEEP
mysqlcounterlimit
Jay
Jay

Reputation: 782

From MySQL 5 table, Increment counter and limit same occurrences

I have a MySQL 5 table as shown below

id emp_id name position
1 D01 Bob Director
2 S01 Jason Staff
3 S02 Mark Staff
4 D02 David Director
5 S03 Rob Staff
6 D04 Will Director
7 S04 James Staff
8 S05 Phil Staff

Per page, I want to display 2 Directors with Staffs under them. Therfore the pages should look like

First page
id emp_id name position
1 D01 Bob Director
2 S01 Jason Staff
3 S02 Mark Staff
4 D02 David Director
5 S03 Rob Staff 
Second page
id emp_id name position
6 D04 Will Director
7 S04 James Staff
8 S05 Phil Staff

Is this possible with MySQL Queries or do I have to write separate function to maintain a counter for Directors? Any help would be highly appreciated. Thanks in advance!

Upvotes: 0

Views: 59

Answers (1)

Akina
Akina

Reputation: 42612

Assuming that Director-Staff relation is defined by the position while sorting by id:

WITH
cte AS ( SELECT id, emp_id, name, position,
                SUM(position = 'Director') OVER (ORDER BY id) + 1 grp 
         FROM test )
SELECT id, emp_id, name, position
FROM cte
WHERE grp DIV 2 = @page_number
ORDER BY id;

I have edited my question and included the MySQL version to 5. – Jay

SELECT t1.id, t1.emp_id, t1.name, t1.position
FROM test t1
JOIN test t2 ON t1.id >= t2.id
WHERE t2.position = 'Director'
GROUP BY t1.id, t1.emp_id, t1.name, t1.position
HAVING (COUNT(*) + 1) DIV 2 = @page_number
ORDER BY t1.id;

https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.6&fiddle=a5b8aec57b01a617e07f3051d149164e

Upvotes: 1

Related Questions