SQLite Forum

Select every nth record from table

Select every nth record from table

(1) By anonymous on 2020-04-07 18:24:04 [link] [source]

I would like to use Select to show every nth record in a table in order rather than all the records.

Any suggestions?

(2.1) By Ryan Smith (cuz) on 2020-04-07 18:35:02 edited from 2.0 in reply to 1 [source]

  FROM (
          FROM t
       ) AS A
 WHERE (A.RowNum % n) = 0

Edit: You need a version supporting Window functions, I think 3.26+ (unconfirmed)

(4) By anonymous on 2020-04-07 18:56:48 in reply to 2.0 [link] [source]

Worked perfect.

NB: This is day 5 of my WFH self taught Sqlite3 class

Much appreciated

(3) By Ryan Smith (cuz) on 2020-04-07 18:41:34 in reply to 1 [link] [source]

While the previous suggestion will work for ANY SELECT query, if you are really only interested in the rows of a single table, and the table is a normal table (not a WITHOUT ROW_ID), then this would suffice:

  FROM t
 WHERE (rowid % n) = 0

(5) By David Raymond (dvdraymond) on 2020-04-08 12:51:30 in reply to 3 [link] [source]

rowid's are not guaranteed to be continuous, so this would only work in certain special situations where they were.