Query to return every 50th record. 1

[Corr]

Hello,

A co-worker had asked me if I can do a query to return every 50th record. I know it can be done, but I do not know how. I also need to return certain columns with each record returned.

Any help is greatly appreciated.

Thanks In Advance,
John

P.S. there are over 20,000 records.

 

[cheyney]

check this out:

http://www.tek-tips.com/viewthread.cfm?spid=220&newpid=220&sqid=352848

With 20,000 records and no sequential key, you might be SOL (if it needs to be fast/frequent)

cheyney

 

[tlbroadbent]

The following example shows a method that is much faster than those in the recommended thread if your table is very large. The example creates a stored procedure. You could just hard code the values in an ad hoc query if you want.

Create Procedure uspReturnEveryNthRow @n int As

--Declare a table variable if using SQL 2000.
--Otherwise create a temp table.
Declare @tbl Table (<Column list>, ID int identity)

--You may want to include an order by clause
Insert @tbl(<column list>)
Select <column list>
From MyDB.dbo.MyTable

Select <column list>
From @tbl
Where ID % @n = 0
Order By ID
Go

Exec uspReturnEveryNthRow @n=50 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Corr]

Thanks again Terry! That worked great as well. However, do you know a way to pick a random number of columns and specify how many? This would be useful if I wanted to pick so many records to have audited.

Thanks,

John

P.S. Thanks for helping. I am relatively new to the SQL language. I'm amazed at how powerful it is.

 

[tlbroadbent]

In Oct 2001, I wrote a FAQ about choosing random records. I assume you meant rows (records) rather than columns. Check this out.

faq183-1143 - &quot;How Can I Select Random Records From a Table with T-SQL?&quot;
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.