every nth row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

every nth row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-26 : 10:35:18

writes "How could I get an SQL query to return every Nth row? For example, it would return the 5th row, the 10th row, the 15th row, etc. I don't want the results to leave the SQL server until they're correct (I don't want to have to throw away some of the results once they're returned). Thank you for your help!"

verronep
Starting Member

15 Posts

Posted - 2002-12-26 : 14:24:26

You can try using the mod function if you put your results in a temp table with an identity column, like so:

DECLARE @T TABLE
(
ukey int IDENTITY(1,1),
data varchar(50)
)

DECLARE @i int
SET @i = 1

-- Populate some test data
WHILE @i < 49
BEGIN
INSERT INTO @T VALUES('Value' + CAST(@i as varchar(50)))
SET @i = @i + 1
END

-- Select only rows where identity column is exactly divisible by 5.
-- You can make the 5 a variable if you want
SELECT * FROM @T
WHERE (ukey % 5) = 0

HTH
Paul

"I have not failed. I have just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources