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 [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 [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.