SQL 2000 Query to select the nth row from a Table

Question

Post reply

SQL 2000 Query to select the nth row from a Table

arun_anand09

SSC Eights!

Points: 845
More actions
April 9, 2009 at 9:22 am

#129997

Guys - this is simple but i been struggling to figure out in 2000 where you cant use MOD function.
I want to display/select every 13th row from a table with 20K rows in it, so basically it should pull every 13th, 26, 39, 52, 65....
Please help and my table is in SQL Server 2000. So pls show solution which is compatable with 2000.
Many Thanks
Arun

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you have an order for them, or just pick which one is record 13 at random?

You could insert into a temp table, with an Identity column, and select the rows where ID%13 = 0. That might work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

arun_anand09 SSC Eights! Points: 845 More actions · Answer 2

Yes sorry I forgot to mention I have a UNIQUEID Column which starts from 1 and I need to pick 13th row based on this Unique ID.

Whats the best and quick solution?

Thanks for responding so quick.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

arun_anand09 (4/9/2009)
Yes sorry I forgot to mention I have a UNIQUEID Column which starts from 1 and I need to pick 13th row based on this Unique ID.
Whats the best and quick solution?
Thanks for responding so quick.

SELECT TOP 1 * FROM (

SELECT TOP 13 * FROM mytable ORDER BY UNIQUEID

) d ORDER BY UNIQUEID DESC

EDIT:apologies, read the spec too quick.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 4

i think th etrick is to simply use integer division to get the mod = 0

here's how to do it with a tally table:

select N from master.dbo.Tally

where n = 1 or n % 13 = 0

and a guestimmate based on what you said about your table structure

SELECT * FROM mytable

where UNIQUEID = 1 or UNIQUEID % 13 = 0

ORDER BY UNIQUEID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arun_anand09 SSC Eights! Points: 845 More actions · Answer 5

That's great both the solution works.

This is a brilliant community - Great work Guys!