Select every nth record from table
(1) By anonymous on 2020-04-07 18:24:04 [link]
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]
~~~ 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)
(3) By Ryan Smith (cuz) on 2020-04-07 18:41:34 in reply to 1
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 ~~~
(4) By anonymous on 2020-04-07 18:56:48 in reply to 2.0 [link]
Worked perfect. NB: This is day 5 of my WFH self taught Sqlite3 class Much appreciated
(5) By David Raymond (dvdraymond) on 2020-04-08 12:51:30 in reply to 3 [link]
rowid's are not guaranteed to be continuous, so this would only work in certain special situations where they were.