Tag

table filter

Browsing

Select every n-th row in mysql table like even or odd.

We have faced some problem that we mysql table pulling entire table but we need a particular nth row order. Suppose we have lot of row in the table like 1000 rows but we need nth order row only, In this case, here is the code how to select every n-th row,  you can get particular n order

Demo                            Download

n_th order like

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 6: Rows 0, 6, 12, 18, ...
n = 22: Rows 0, 22, 44, 66, ...

Step 1:  Consider the same table in previous post

nth_order show in mysql

Step 2 : very simple method

SELECT * FROM exam WHERE (id % 2) = 0; # even
SELECT * FROM exam WHERE (id % 2) > 0; # odd

Here showing the nth order in even and odd id, that’s it very simple

nth_order show in mysql in even

or Another method for sorting nth order is

set @row:=-1;
SELECT exam. *
FROM exam

INNER JOIN (
SELECT id
FROM (
SELECT @row:= @row +1 AS rownum, id
FROM (
SELECT id
FROM exam
ORDER BY id
) AS sorted
) AS ranked
WHERE rownum %2 =0
) AS subset ON subset.id = exam.id
LIMIT 0 , 50;

Set @row as 1 and add with rownum the div with your nth order

rownum %2 =0   // 2 show 1,3,5,7,9..
rownum %3 =0   // 3 show 1,4,7,10,13..
rownum %3 =0   // 3 show 1,5,9,13..

Enjoy code