CODEWITHSUNDEEP
phpmysql
user990767
user990767

Reputation: 1019

How to select every nth record?

I'm trying to select a specific set of records which should work like this:

query 1 should select ID's = 1,2,3 skip 4 - 5,6,7 skip 8 - 9,10,11 skip 12 and so on. query 2 should select only every 4th ID = 4,8,12,16 and so on.

I found this code to select every 4th record:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, noun
    FROM (
        SELECT @row :=0) r, nouns
    ) ranked
WHERE rownum %4 =1

I didn't test it yet, but I will just assume that it works, but how can I do the first query ?

Thanks

Upvotes: 0

Views: 2379

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65264

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, noun
    FROM (
        SELECT @row :=0) r, nouns
    ) ranked
WHERE rownum %4 =1

will select the (0th,) 4th, 8th, .. row. In analogous way you can select the others:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, noun
    FROM (
        SELECT @row :=0) r, nouns
    ) ranked
WHERE rownum %4 <> 1

This SQL could be shortened to

SELECT
    @row := @row +1 AS rownum, noun
FROM
  (SELECT @row :=0) r,
  nouns
WHERE (@row % 4) [=|<>] 1

This could be quite significant, as I suspect MySQL will use a temp table on the long form but not on the shorter form (not verified)

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56357

select * from table where id mod 4 <> 0

Upvotes: 2

Related Questions