Selecting every Nth record from an ordered table

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

Transact-SQL (2000)

Selecting every Nth record from an ordered table

Author

Topic

tizer001
Starting Member

9 Posts

Posted - 2002-09-19 : 06:41:20

Hi,
I have a problem and would like to know if it can be solved simply via SQL Server 2000.
An example:- I have 1,000,000 rows selected by order of postcode/zipcode. From this I want to extract a spread of records evenly across the ordered table, e.g. every 10th row.
The number of records in the table I use vary and also the number of records I require to select varies too. It could be every 25th row from a table of 10,000.

Can this be done in SQL? I am assuming that by the general design of a relational database that this is not very straight forward. Also, I'm a bit of a novice.

At the moment I have a simple C program which does the job on CSV files but would really like to implement it in SQL to save exporting and re-importing the data as I can have as many as 50,000,000 rows in the table.

Thanks for any help/comments.
Dave.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-19 : 07:25:55

No this is actually quite easy, especially if you have a 'RowNum' type primary key (int IDENTITY(1,1) perhaps).

You can use the modulo (%) operator in a condition on the 'RowNum' in a where clause.....where rownum % @Nth = 0....

If you don't have a 'RowNum', you could either create a temp table with one, or the condition could be based on the count of records less than ....

Jay White
{0}

Edited by - Page47 on 09/19/2002 07:27:11

tizer001
Starting Member

9 Posts

Posted - 2002-09-19 : 11:31:55

Hi, thanks for the reply. Problem is I don't have a RowNum and never will.
As I said I'm a novice.

"If you don't have a 'RowNum', you could either create a temp table with one, or the condition could be based on the count of records less than .... "

I'm not really sure where your sentence above was going but it would be a great help if you could complete it in a little more detail.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 11:47:24

Take a look at these articles:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=row+number

You'll be able to use at least one of these techniques to add a row number, which you can then apply Jay's code to.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-19 : 11:52:40

EDIT: Follow Rob's link first ...


set nocount on

create table #tizer000 (pk int)
declare @i int
set @i = 1
while @i < 1000
begin
    insert #tizer000
    select @i
    select @i = @i + 1
end
go
-- First method, build a temp table with RowNum
declare @t datetime
set @t = getdate()
select
    IDENTITY(int,1,1) as RowNum,
    pk
into
    #tizer000_2
from
    (select top 100 percent
        pk
     from
        #tizer000
     order by pk) as a

select
    pk
from
    #tizer000_2
where
    rownum % 10 = 0

select 'This took ' + convert(varchar,datediff(ms,@t,getdate())) + 'ms.'
drop table #tizer000_2
go

-- Second method, where condition based on the count of records less ...
declare @t datetime
set @t = getdate()

select
    pk
from
    #tizer000 t
where
    (select count(*) + 1 from #tizer000 where pk < t.pk) % 10 = 0
select 'This took ' + convert(varchar,datediff(ms,@t,getdate())) + 'ms.'
go
drop table #tizer000
go

As you will probably see from this example, it can be much more efficient to build the temp table. HOWEVER, YMMV based on how many records you are working with, indexing, etc. I'd recommend trying both ...

Jay White
{0}

Edited by - Page47 on 09/19/2002 11:54:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources