Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Getting Every nth Row from a Table

You can use the Vertica MOD function to grab every _n_th 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)

Suppose you want every 2nd row from the table above. You 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, 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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.