Getting Every Nth Row from a Table (Enhanced)

In a previous Vertica Quick Tip we learned how to query every Nth row from a table when we had a unique ID column having sequential values (that is, from a sequence).

What if we don’t have a key like that in our table? No problem thanks to the ROW_NUMBER() Analytic Function!

Say I have this data in a table named TAB:

dbadmin=> SELECT * FROM tab;
c1     |    c2
-----------+-----------
GTMIJYZTB | EYEAVFTAO
IHAMDNYOO | ARBJXZHNN
IMCTCCEMB | KIHTTXSVY
JXAPTMKMT | HAXAWTUYM
OHSNIRKBS | LALXUSOXY
QSBCDXCUH | PEEWGUHNV
SDXUXNBSK | BSBQMHLDA
UNCMJPNRM | IBWDYLYAR
UWYBZMDTG | QGDHAUQDF
VJCYYNAVB | TTFPVTOLG
(10 rows)

I can get every second row by running the following query:

dbadmin=> SELECT * FROM (SELECT *, row_number() over() rn FROM tab) foo WHERE foo.rn % 2 = 0;
c1     |    c2     | rn
-----------+-----------+----
IHAMDNYOO | ARBJXZHNN |  2
JXAPTMKMT | HAXAWTUYM |  4
QSBCDXCUH | PEEWGUHNV |  6
UNCMJPNRM | IBWDYLYAR |  8
VJCYYNAVB | TTFPVTOLG | 10
(5 rows)

Or for every third row, I’d run this query:

dbadmin=> SELECT * FROM (SELECT *, row_number() over() rn FROM tab) foo WHERE foo.rn % 3 = 0;
c1     |    c2     | rn
-----------+-----------+----
IMCTCCEMB | KIHTTXSVY |  3
QSBCDXCUH | PEEWGUHNV |  6
UWYBZMDTG | QGDHAUQDF |  9
(3 rows)

Have fun!