CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
serdar
serdar

Reputation: 454

How to select only every n-th row in table?

I have a table.It has more than 100 row and increasing. I want to get rows like this :

1  - row (not needed 2,3,4,5,6,7. rows)
8  - row  
15 - row
22 - row
29 - row

note : at MSSQL 2008 R2

Upvotes: 2

Views: 80

Answers (2)

Yugandhar
Yugandhar

Reputation: 87

--It Helps to you

CREATE TABLE ##Numeric (Id INT)

DECLARE @Value INT = 1

WHILE (@Value <= 100)
BEGIN
    INSERT INTO ##Numeric
    SELECT @Value

    SET @Value = @Value + 1
END

SELECT *
FROM ##Numeric
WHERE ID % 7 = 1

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460028

You can use ROW_NUMBER and %, f.e. with a common-table-expression:

WITH CTE AS
(
  SELECT t.*, RN= ROW_NUMBER() OVER (Order By OrderColumn ASC)
  FROM dbo.TableName t
)
SELECT * FROM CTE WHERE RN % 7 = 1

Upvotes: 5

Related Questions