MySQL - How to take records based on a particular sequence

Question

I would like to extract a number of records on a table based on a sequence e.g. n x 4. Which would mean I would need 0, 4, 8, 12, 16, etc...

Reading through the closest I found to what I'm looking for is:

SELECT * FROM table ORDER BY ID LIMIT n-1,1

I would like to see if it is possible to be done using just one query.

score 0 · Answer 1 · answered May 14 '19 at 06:00

0

If you are using MySQL 8+, then ROW_NUMBER can be used here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) - 1 rn
    FROM yourTable
)

SELECT *
FROM cte
WHERE rn MOD 4 = 0
ORDER BY ID;

answered May 14 '19 at 06:00

Tim Biegeleisen

Mohideen bin Mohammed · Answer 2 · 2019-05-14T06:06:48.210

0

you can use mod = 0

select * from table where ID mod 4 = 0;

If you have your PK as integer or rowID you can simply use above query. or else have to count manually without using row_number.

query in elaborate,

select * from (SELECT t.*, 
       @rowid := @rowid + 1 AS ID
  FROM TABLE t, 
       (SELECT @rowid := 0) dummy) A where A.ID mod 4 = 0;

edited May 14 '19 at 06:06

answered May 14 '19 at 06:01

@TimBiegeleisen i gave a syntax. now i modified .. ROW_NUMBER() wasn't there in all version.. as you mentioned mysql 8+ .. so i am done with row_count myself – Mohideen bin Mohammed May 14 '19 at 06:08

score 0 · Answer 3 · answered May 14 '19 at 06:31

0

You can make use of MOD function, it checks with the remainder after division of two values. So accordingly your query will go as follows

SELECT * 
FROM table
WHERE ID MOD 4 = 0  ORDER BY ID

answered May 14 '19 at 06:31

gauri

3 Answers3