> Jim Gurfein wrote:
>> Hi List,
>>
>> 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 ?
> If you have some consecutive number use the MOD operator
> in your WHERE.
This might work provided there's never a problem inserting a record with an
autonumber field in at least MS SQL Server ( a failed insert still chews up
a record number ), and have never deleted anything from the table...
It's crude, but you could also use something like this
CREATE PROCEDURE sp_getNthRecord
@N
AS
DECLARE @mytableID INT;
DECLARE @X INT;
SET @X = 1;
CREATE TABLE #N ( mytableID INT );
DECLARE curN CURSOR LOCAL FAST_FORWARD FOR
SELECT mytableID FROM mytable
OPEN curN;
FETCH NEXT FROM curN INTO @mytableID;
WHILE (@@FETCH_STATUS = 0) BEGIN
IF (@X % @N = 0) INSERT INTO #N (mytableid) VALUES (@mytableID);
FETCH NEXT FROM curN INTO @mytableID;
SET @X = @X +1;
END
CLOSE curN;
DEALLOCATE curN;
SELECT * FROM mytable WHERE mytableid
INNER JOIN #N ON ( #N.mytableid = mytable.mytableid );
DROP TABLE #N;
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
