MySQL 8.0

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

CREATE TABLE tablename(id INT, relevance INT, tags VARCHAR(100), column1 VARCHAR(10), column2 VARCHAR(10))
INSERT INTO tablename(id, relevance, tags, column1, column2) VALUES
(1, 6, 'tag1', 'a1', 'a2'),
(2, 1, 'tag2', 'b1', 'b2'),
(3, 6, 'tag1', 'c1', 'c2'),
(4, 6, 'tag1', 'd1', 'd2'),
(5, 2, 'tag4', 'e1', 'e2'),
(6, 6, 'tag1', 'f1', 'f2'),
(7, 6, 'tag1', 'g1', 'g2'),
(8, 6, 'tag1', 'h1', 'h2'),
(9, 6, 'tag1', 'i1', 'i2'),
(10, 6, 'tag1', 'j1', 'j2'),
(11, 3, 'tag7', 'k1', 'k2'),
(12, 6, 'tag1', 'l1', 'l2'),
(13, 6, 'tag1', 'm1', 'm2'),
(14, 6, 'tag1', 'n1', 'n2'),
(15, 6, 'tag1', 'o1', 'o2');
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY tags = 'tag1' ORDER BY relevance) rn
  FROM tablename  
)
SELECT *
FROM cte
ORDER BY CASE 
  WHEN tags = 'tag1' THEN rn + FLOOR((rn - 1) / 4)
  ELSE (4 + 1) * rn  
END
LIMIT 10;


id
relevance
tags
column1
column2
rn


1
6
tag1
a1
a2
1

3
6
tag1
c1
c2
2

4
6
tag1
d1
d2
3

6
6
tag1
f1
f2
4

2
1
tag2
b1
b2
1

7
6
tag1
g1
g2
5

8
6
tag1
h1
h2
6

9
6
tag1
i1
i2
7

10
6
tag1
j1
j2
8

5
2
tag4
e1
e2
2

id	relevance	tags	column1	column2	rn
1	6	tag1	a1	a2	1
3	6	tag1	c1	c2	2
4	6	tag1	d1	d2	3
6	6	tag1	f1	f2	4
2	1	tag2	b1	b2	1
7	6	tag1	g1	g2	5
8	6	tag1	h1	h2	6
9	6	tag1	i1	i2	7
10	6	tag1	j1	j2	8
5	2	tag4	e1	e2	2