return every nth record

[747576]

Hi,

I am trying to write a query to return every nth record i.e. every 20th

I am using the sql:

select * from TABLE
where RIGHT(ID,4) % 20 = 0 
and ID like 'TEST%'

where the ID is like 'TEST0001' and so on. I have 100 records for example but if returning every 20th record I'd expect my first record id from the query to be TEST0020, but the first record seems to always be the 30th.

Can someone check the sql, I am using sql2000

Thank You

 

[RiverGuy]

When you say the 30th record, are you saying it is returning TEST0030? I'm not sure why that could be happening. If I try SELECT '0030' % 20, I get a result of 10 (using SQL 2005 however). Maybe you should try casting to INT first.

 

[PWise]

in what order are your records

 

[gmmastros]

try...

select * from TABLE
where RIGHT(ID,4) % 20 = 0
and ID like 'TEST%'
[!]Order By ID[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom