Stepping through Results - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Stepping through Results

Author

Topic

kittles3069
Starting Member

17 Posts

Posted - 2007-08-09 : 16:01:40

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 divided by the sample amount (performming that calculation before its sent here)
@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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 16:07:20

If you are using SQL Server 2005, use NTILE() function or ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

kittles3069
Starting Member

17 Posts

Posted - 2007-08-09 : 17:05:53

quote:
Originally posted by Peso

If you are using SQL Server 2005, use NTILE() function or ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

Thanks for the fast reply Peso.

I looked at both functions you suggested, and I may not be using them correctly as i dont understand their specific purpose.

They both seem to alleviate the problem I was having with not returning enough items, but now the records returned appear in a sequential order from the table. I cannot use 40 sequential records. They would need to be something like the 1st, 11th, 21st......111th record, etc...

Here is the query i used:


SELECT *
FROM (
     SELECT TOP 40 *
     FROM (
          SELECT TOP 41  SerialNumber NTILE(41) OVER (ORDER BY SerialNumber ASC) AS Ordered
          FROM TABLEA 
          ORDER BY SerialNumber ASC) AS Foo
     ORDER BY SerialNumber DESC) AS Bar
ORDER BY SerialNumber ASC

kittles3069
Starting Member

17 Posts

Posted - 2007-08-10 : 13:50:46

I finally figured it out.. Thanks for the help.


'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'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-10 : 16:04:07

SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RecID FROM Table1
) AS d
WHERE d.RecID % 45 = 0

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources