I can see using 2 options for doing this.. 1. ColdFusion Way. <cfquery name="whatever"> select UniqueIDField from TableName order by UniqueIDField </cfquery> <cfset aRowids=arrayNew(1)> <cfloop query="whatever"> <cfif NOT whatever.currentRow mod 15> <cfset ArrayAppend(aRowids,aRowids.UniqueIDField)> </cfif> </cfloop> <cfset lRowids=arrayToList(aRowids,",")> <cfquery name="MyQuery"> select * from TableName where UniqueIDField in(variables.lRowids) </cfquery> If you dont want to do an IN... u can create a Temptable and use a Join.
2. SQL server procedure using cursor(Cursors are slow in SQL Server) declare @row int,@Cloop int,@rowid int,@Listids varchar(5000) set @Cloop=1 set @Listids='' declare C1 Cursor for select UniqueIDField from TableName order by UniqueIDField OPEN C1 FETCH NEXT FROM C1 into @rowid While(@@FETCH_STATUS=0) BEGIN IF @Cloop % 15 = 0 set @Listids=@Listids+','+cast(@rowid as varchar(10)) FETCH NEXT FROM C1 into @rowid set @Cloop=@Cloop+1 END set @Listids=subString(@Listids,2,len(@Listids)) PRINT @Listids CLOSE C1 DEALLOCATE C1 select * from TableName where UniqueIDField in(@Listids) If you dont want to do an IN... u can create a Temptable and use a Join. Joe Certified Advanced ColdFusion Developer [EMAIL PROTECTED] -----Original Message----- From: Jim Gurfein [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 01, 2002 8:41 PM To: CF-Talk Subject: Re: Nth Record Selection Set Your second selection concept was correct... eg :every third or 15th or 20th record At 10:22 AM 9/1/2002 -0400, you wrote: > > Jim Gurfein wrote: > >> Hi List, > >> > >> <Sorry for sending this again, but the list was having > >> problems > > >> > >> All you gurus out there... I'm trying to find a way to > >> select every nth > >> record from a database that has 200,000 records. How > >> would I go about > >> setting up the SQL to select every 15th record ? > >> > >> Any and all help would be appreciated! > > > In MySQL, PostgreSQL and such: > > > SELECT * FROM table LIMIT startrow, numberofrows > > > so the 15000th item en the 15 following it would be: > > > SELECT * FROM table LIMIT 15000, 15 > >Oh duh... I didn't even think to ask... Did you mean for pagination, i.e. > >1 >2 >3 >4 >5 >next page > >Or did you literally mean > >1 >2 >3 <- select this >4 >5 >6 <- and this >7 >8 >9 <- and this > >?? > >Isaac >Certified Advanced ColdFusion 5 Developer > >www.turnkey.to >954-776-0046 > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
