Vertica Quick Tip: Getting Every n-th Row from a Table

Posted March 8, 2018 by Soniya Shah, Information Developer

Three 3D arrows, different colors pointing in different directions
This blog post was authored by Jim Knicely. You can use the Vertica MOD function to grab every nth row from a table. Example: dbadmin=> SELECT * FROM nth_row ORDER BY 1; id | some_data ----+----------- 1 | TEST1 2 | TEST2 3 | TEST3 4 | TEST4 5 | TEST5 6 | TEST6 7 | TEST7 8 | TEST8 9 | TEST9 10 | TEST10 (10 rows) Say I want every 2nd row from the table above. I can run the following query to get them: dbadmin=> SELECT * FROM nth_row WHERE mod(id, 2) = 0; id | some_data ----+----------- 2 | TEST2 4 | TEST4 6 | TEST6 8 | TEST8 10 | TEST10 (5 rows) Or for every 3nd row, I’d run this query: dbadmin=> SELECT * FROM nth_row WHERE mod(id, 3) = 0; id | some_data ----+----------- 3 | TEST3 6 | TEST6 9 | TEST9 (3 rows) Have Fun!