How to skip n rows (i = n) in MySQL results?-CodePudding

Imagine I have a table with column value with perfectly ordered rows 1 till 100.

How do i skip 5 rows in the the results instead of 1, 2, 3... I got 1, 6, 11, 16...

Is this thing even possible?

CodePudding user response：

Using modulo operator you can achieve such result.


SELECT * FROM table WHERE id % 5 = 1

It can be written in three forms

MOD(N,M) => select * from table where mod(id, 5) = 1
N % M  => as shown above
N MOD M => select * from table where (id mod 5) = 1

CodePudding user response：

If I understand your question this is a job for MOD.

SELECT id FROM table WHERE (id MOD 5) = 1

You asked about imperfect ordering. I believe you mean a situation where some id values are missing. For example, if you have

1  2  3  5  6  7  8  9 10 11 12 13

you want a result of

1  7 12

The answer to that question depends on the version of MySQL you use. If you have a "modern" version (MySQL 8 , MariaDB 10 ) you can use a window function to get the row number, then use MOD.

SELECT * FROM (
       SELECT  *,
               ROW_NUMBER() OVER (ORDER BY id) AS rownum
         FROM  table
) a
WHERE rownum MOD 5 = 1

If you're using a more primitive version of MySQL you can fake the ROW_NUMBER window function with session variables.

SELECT *
FROM (
              SELECT detail.*,
                    (@row_number := @row_number   1) AS rownum
               FROM (SELECT * FROM table ORDER BY id) detail
               JOIN (SELECT @row_number := 0) AS init
            ) a
WHERE rownum MOD 5 = 1

This query is more painful to write because you must apply ORDER BY to the rows of your detail table before you do the @row_number work. Hence the doubly nested SELECT queries.