Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: SQL selecting every nth row of a table

    Is it possible to write a select statement where you only retrieve every 5th row in the table?

    Thanks in advance

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    If you have OLAP functionality, use...
    Code:
    select col1, col2 ...
    from (select col1, row_number()over() rn
          from yourTable) aTempTable
    where mod(rn,5) = 0
    ;
    If you don't have OLAP functionality, use...
    Code:
    select col1, col2 ...
    from (select a.col1, a.col2 ... , count(*) rn
           from yourTable a
           ,    yourTable b
           where a.aUniqueIndexCol >= b.aUniqueIndexCol
           group by a.col1, a.col2 ...
         ) aTempTable
    where mod(rn,5) = 0
    ;
    The second example would involve a slightly more complicated join if your table has a composite unique key (and if your table has no unique identifier, you're outta luck!)

    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •