Retrieve only every Nth row of a given recordset

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)

Retrieve only every Nth row of a given recordset

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-14 : 08:11:21

Peter writes "i am using SQL Server 2000 on a Win 2000 Server.

My Problem:

I have large tables (>10.000.000 records)

Since I need the data passed to my application only for displaying them in a diagram I do not need each value.

So I followed the approach to pass only every Nth record depending on the total number of records from the query.

e.g. total number = 10000 I only want every third record
total number = 30000 every fith record
and so on

Any help on this would be very appreciated

Peter"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-14 : 08:29:24

well if you have an identity column you can do
select *
from myTable
where id%3=0 -- id is your identtiy column.

other ways may be really slow on a 10 mil. row table...

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-14 : 08:59:40

quote:
Since I need the data passed to my application only for displaying them in a diagram I do not need each value

Why not just pick the top N rows that you need? Much easier to write and will be faster as it does not have to traverse the entire table.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-14 : 09:21:36

"Why not just pick the top N rows that you need? Much easier to write and will be faster as it does not have to traverse the entire table.".....and if you include an ORDER BY NEWID() clause you can get a random subset of your data, which might be 'better/more meaningful for you'

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-14 : 09:50:08

OK, I'd like to know what possible value it is to display data in this manner...anyone?

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-14 : 09:59:17

I asked myself that question last night when I was reading this post, but decided I would only lose valuable sleep over it. I chose sleep instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources