Every 10th Rows 1

[chaoma]

I have about 100,000 records. My boss wants records every 10th rows (not random). Does anyone know how to do this. Thanks.

 

[gmmastros]

One idea...

Insert the data in to a temp table with an identity column. Then select the data from the temp table where the identity column MOD 10 = 0. In SQL Server, the mod operator is the percent sign. Ex.

[tt]
declare @Temp Int

Set @Temp = 26

Select @Temp % 10
[/tt]

For performance reasons, you may want to ONLY put the unique identifier in to the temp table and then join the temp table back with your 'real' table to get the final output.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chaoma]

Thank you. That is a good idea. Thanks.

 

[mrdenny]

To ensure that you have every 10th record you'll need a cursor that puts every 10th record into a temp table then select all the records from the temp table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chaoma]

I thought I understand everything.

How do you insert a unique identifier?

Thanks.

select ?, a.* into tableB from tableA

 

[gmmastros]

Create Table #Temp(RowId Integer Identity(1,1), Field1 int, Field2 varchar(20), etc...)

Insert Into #Temp(Field1, Field2)
Select Field1, Field2
From   Table

Select * 
From   #Temp
Where  RowId % 10 = 1

This isn't tested, but should get you close. Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry. I missed the most important part (well one of them anyway). When inserting records in to the temp table, you'll want to sort them in some way so that when you select every 10th row, the output will be right.

Create Table #Temp(RowId Integer Identity(1,1), Field1 int, Field2 varchar(20), etc...)

Insert Into #Temp(Field1, Field2)
Select Field1, Field2
From   Table
[!]Order By SomeField[/!]

Select * 
From   #Temp
Where  RowId % 10 = 1

Also notice that I changed the MOD to use 1 instead of 0. When using 0, you would not get the first record, you would get records 10, 20, 30, etc... when using mod 1, you get 1, 11, 21, 31, etc... I'm not sure which way you want, but now that you understand how mod works, you can make a more informed decision.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chaoma]

Works great. Thanks.

 

[SQLDenis]

;-)


select replace('Works great. Thanks.','Works great. Thanks.','Works great George. Thanks. Here is a star for you')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

syntax error near REPLACE function

function expected two parameters, got three

default error function "awardstar" invoked by username interested3rdparty

r937.com | rudy.ca