The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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.