SQLite Forum

Select every nth record from table
Login

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 [link] [source]

SELECT * 
  FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY 1) RowNum, *
          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 [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:

SELECT *
  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.