Stepping through Results

[Midnight Marauders]

Hi all..

I am looking for some assistance on a query.

I have a Stored Procedure where I am attemtping to step through the results and return every other nth record..

Here is what I have so far:

SELECT * FROM (SELECT TOP(' + @SampleAmount + ') * FROM (SELECT DISTINCT TOP(' + @TotalRecords + ') * FROM ' + @TableName + ' WHERE CountyNumber = ' + @CountyNumber + ' ORDER BY ID ASC) As Foo  ORDER BY ID DESC) As Bar  ORDER BY ID ASC'

@SampleAmount is the amount of records I want retruned
@TotalRecords is the total amount of records available
@TableName is duh, the table from which the recrods come
@CountyNumber is the county number to filter the results

This works fantastic for really large recordsets, like 10,000.. But i am seeing some inconsistancies when trying to step through a smaller amount.

Say There are only 700 TotalRecords availale, and I want 100 records returrned, with the above SP i woudl only get 7 records..If i wanted 40 records I would get 17 i think, and i think they exact cutoff where i would get exactly what i request is 26 records..

There is obviuously a flaw in my logic, can anyone assist in this??

Thanks in advance for any help!

[bwestover]

From what I can tell your code above will only return as many records as you specify in @SampleAmount

You could open a cursor and then have it insert every Nth record into a table variable, or temp table. Then return that as your recordset at the end of the procedure.

Example:

--Controlling Variables
Declare @TableName varchar(30)
Declare @CountyNumber int
Declare @SampleAmount int
 
--Processing Variables 
Declare @return_table table ([ID] int, [Data] varchar(25), [CountyNumber] int)
Declare @ID int
Declare @Data varchar(25)
Declare @County int
Declare @Counter int
Declare myCursor scroll cursor for
Select * 
From ' + @TableName + '
Where CountyNumber = ' + @CountyNumber + '
Order By ID ASC
 
Set @Counter = 0
 
FETCH NEXT FROM myCursor into @ID, @Data, @County
 
While @@fetch_status = 0
Begin
 
--Each time we load a record increment the counter by 1
Set @Counter = @Counter + 1
 
--If we've reached our Nth record reset the counter, then insert the current record
If @Counter = @SampleAmount
    Begin
    Set @Counter = 0
    Insert into @return_table
    (ID, Data, County)
    Values
    (@ID, @Data, @County)
    End
 
FETCH NEXT FROM myCursor into @ID, @Data, @County
End
 
Close myCursor
Deallocate myCursor
 
--When were all done display out the results
Select * From @return_table

[Midnight Marauders]

THanks bwestover...

I was actually just coming to post that I had finally figured it out when I saw your reply. Thanks for the effort. It looks like our solutions are quite different.

'SELECT *
FROM
(
     SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY NTileNum ORDER BY ID ASC)
     FROM
     ( 
          SELECT *, NTILE(' + @SampleAmount + ') OVER (ORDER BY ID) AS NTileNum
          FROM ' + @TableName + '
          WHERE CountyNumber = ' + @CountyNumber + '
     ) As A
) As B
WHERE RowNumber = 1'
 

From what I can tell your code above will only return as many records as you specify in @SampleAmount

You could open a cursor and then have it insert every Nth record into a table variable, or temp table. Then return that as your recordset at the end of the procedure.

Example:

--Controlling Variables
Declare @TableName varchar(30)
Declare @CountyNumber int
Declare @SampleAmount int
 
--Processing Variables 
Declare @return_table table ([ID] int, [Data] varchar(25), [CountyNumber] int)
Declare @ID int
Declare @Data varchar(25)
Declare @County int
Declare @Counter int
Declare myCursor scroll cursor for
Select * 
From ' + @TableName + '
Where CountyNumber = ' + @CountyNumber + '
Order By ID ASC
 
Set @Counter = 0
 
FETCH NEXT FROM myCursor into @ID, @Data, @County
 
While @@fetch_status = 0
Begin
 
--Each time we load a record increment the counter by 1
Set @Counter = @Counter + 1
 
--If we've reached our Nth record reset the counter, then insert the current record
If @Counter = @SampleAmount
    Begin
    Set @Counter = 0
    Insert into @return_table
    (ID, Data, County)
    Values
    (@ID, @Data, @County)
    End
 
FETCH NEXT FROM myCursor into @ID, @Data, @County
End
 
Close myCursor
Deallocate myCursor
 
--When were all done display out the results
Select * From @return_table

[bwestover]

Quite right. Very different indeed.

I can see you are using the new ranking features introduced in SQL 2005. Im still mostly coding in 2000 and Im not overly familiar with those functions. It looks much cleaner than having to declare a scrollable recordset.

Probably much faster as well.

Im glad you got it figured out.