Getting Every nth Row from a Table

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

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.