MySQL select rows in specific iteration (1st, 5th, 10th, 15th, n++)

Published on August 22, 2020 1:31:19AM by Administrator

MySQL query to select rows on specific iteration.


MySQL select rows in specific iteration (1st, 5th, 10th, 15th, n++)

There are sometimes that we want to select rows with specific iteration for example we have 10 records with id [2, 3, 5, 6, 7, 10, 11, 12, 13, 14] and we want to get all record but skip every 2 record next eg [2, 6, 11,14].

Below here are sample of query you can use to achieve this objective.

SELECT * FROM (
 SELECT @row:[email protected]+1 AS rownum, id FROM (SELECT @row:=[startRowNum]) r, [tableName]
) ranked
WHERE (rownum%[n]=0 OR rownum=1)

Replace the following based on your environment

  • startRowNum= The starting row number, usually we put here 0
  • tableName = Target table
  • n = The number of row to skip