Get MySQL result where every Nth row is needed item

Question

I have one problem with mysql query, if it's possible.

Example: we have table with products, each product has field type, it could be "free" or "gold". When we search all products by keyword, we get results like

id | product_name | ... | type
---+--------------+-----+-----
1  | ...          | ... | free
2  | ...          | ... | gold
3  | ...          | ... | free
4  | ...          | ... | free
5  | ...          | ... | free
6  | ...          | ... | gold
7  | ...          | ... | free
8  | ...          | ... | free
9  | ...          | ... | gold
10 | ...          | ... | free
11 | ...          | ... | gold
12 | ...          | ... | free
13 | ...          | ... | free
14 | ...          | ... | gold
15 | ...          | ... | free
16 | ...          | ... | gold
17 | ...          | ... | free
18 | ...          | ... | free
19 | ...          | ... | free

The problem is: how to order this result to get each 4th row type "gold"

id | product_name | ... | type
---+--------------+-----+-----
1  | ...          | ... | free
3  | ...          | ... | free
4  | ...          | ... | free
2  | ...          | ... | gold
5  | ...          | ... | free
7  | ...          | ... | free
8  | ...          | ... | free
6  | ...          | ... | gold
10 | ...          | ... | free
12 | ...          | ... | free
13 | ...          | ... | free
9  | ...          | ... | gold
15 | ...          | ... | free
17 | ...          | ... | free
18 | ...          | ... | free
11 | ...          | ... | gold
19 | ...          | ... | free
14 | ...          | ... | gold
16 | ...          | ... | gold

I have no idea how to resolve it. Is it possible?

I know how to resolve it with PHP, but I need to know if I can do it with MySQL query!

Answer 1

You can use the following query:

SELECT id, product_name, type, r
FROM ( 
SELECT id, product_name, type,  
       IF((@r1+1) % 4 = 0, @r1:= @r1 + 2, @r1:= @r1 + 1) AS r
FROM mytable, (SELECT @r1:=0) AS var
WHERE type <> 'gold'
ORDER BY id ) t


UNION ALL

SELECT id, product_name, type,  r*4 AS r
FROM (
SELECT id, product_name, type,  
       @r2:= @r2+1 AS r
FROM mytable, (SELECT @r2:=0) AS var
WHERE type = 'gold' ) s
ORDER BY r

Explanation:

• The first part of the UNION retrieves all non-'gold' records and assigns r values equal to 1,2,3,5,6,7,9,.. to them.
• The second part retrieves all 'gold' records and assigns r values equal to 4,8,12,... to them.
• ORDER BY r is applied to the whole set return by UNION ALL and enforces the required ordering.

Demo here