Clever Geek Handbook

Get MySQL result where every Nth row is a required item

I have one problem with a mysql query if possible.

Example: we have a table with products, each product has a field type, it can 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 every fourth string 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 don't know how to solve it. Is it possible?

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

+3


source to share


2 answers


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 UNION

    extracts all non-gold records and assigns them r

    equal values 1,2,3,5,6,7,9,..

    .
  • The second part extracts all gold records and assigns them r

    equal values 4,8,12,...

    .
  • ORDER BY r

    applies to the entire return set UNION ALL

    and provides the desired ordering.

Demo here

+2


source


If you want "gold" on every fourth row, list "gold" and list everything else. Then you can combine the results and use arithmetic to get gold on every fourth line:



select t.*
from ((select t.*, (@rng := @rng + 1) as rn
       from table t cross join
            (select @rng := 0) params
       where type = 'gold'
      ) union all
      (select t.*, (@rn := @rn + 1)
       from table t cross join
            (select @rn := 0) params
       where type <> 'gold'
      )
     ) t
order by (case when type = 'gold' then rn
               else 1 + floor((rn - 1) / 3)
          end),             -- get groups of 3 non-gold and 1 gold
         (type = 'gold') ,  -- put the gold last in the group
         id                 -- order the rest by id
+2


source






More articles:


All Articles