Select (10 to 25) Percent

If what you try to achieve is pagination then bellow is something that helps. For easiness the table is just  one column with numbers but you can adapt.

DECLARE
	@sql varchar(1000),
	@rows_per_page int,
	@page_number int
-- set the number of rows per page
SELECT
	@rows_per_page=25
 
 
 
-- set the page number you want to retrieve
SELECT
	@page_number=2
 
-- SQL 2000 version using dynamic query
SELECT @sql='
SELECT 
	* 
FROM 
	numbers act 
	INNER JOIN 
	(
		SELECT TOP '+cast(@rows_per_page as varchar)+' 
			 number
		FROM 
		(
			SELECT TOP '+cast(@page_number*@rows_per_page as varchar)+'
				 number
			FROM 
				numbers
			ORDER BY 
				number ASC
		) AS Act1 
		ORDER BY 
			number DESC  
	) AS Act2 
		ON Act2.number=act.number
ORDER BY 
	act.number ASC
'
 
EXEC (@sql)
 
 
 
 
 
 
-- SQL 2005 version where variables can be used in TOP clauses
SELECT 
	* 
FROM 
	numbers act 
	INNER JOIN 
	(
		SELECT TOP (@rows_per_page)
			 number
		FROM 
		(
			SELECT TOP (@page_number*@rows_per_page)
				 number
			FROM 
				numbers
			ORDER BY 
				number ASC
		) AS Act1 
		ORDER BY 
			number DESC  
	) AS Act2 
		ON Act2.number=act.number
ORDER BY 
	act.number ASC

Select allOpen in new window

SELECT * 
  FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY YourPrimaryKeyColumn) RN FROM YourTable) Tab
 WHERE RN BETWEEN (SELECT COUNT(*) FROM YourTable)*0.1 AND (SELECT COUNT(*) FROM YourTable)/4.0

Select allOpen in new window

Sharath, it would be nice if you explain wht that NASAlike query does so maybe we can understand that is really useful. :o)

Thanks Guys.

I have accepted CGLuttrells answer because it was first and most desirable, though I couldn't get it to work because I do have an XML collumn which Sharath pointed out why.  The rest of the answers are all acceptable solutions.

Though it sure would be nice to have a Between that was as simple to addin without extra thinking like TOP.

Thank you all.

Corey

>> I  have accepted CGLuttrells answer because it was first and most desirable, though I couldn't get it to work because I do have an XML collumn which Sharath pointed out why. << 
Even though you don't have XML data type column in your table, you can not use CGLuttrells's solution. In order to use CGLuttrells solution (assuming you dont have XML data type column), you need to wrap the two queries  as derived tables and then only you can apply EXCEPT.

See the example.

create table #Temp1(col1 int)
declare @int int
set @int = 1
while @int<=100
begin
insert into #Temp1 values(@int)
set @int = @int + 1
end
select top 25 * from #Temp1 order by col1
except
select top 10 * from #Temp1 order by col1
/*
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'except'.
*/
 
select * from (select top 25 * from #Temp1 order by col1) t1
except
select * from (select top 10 * from #Temp1 order by col1) t2
 
drop table #Temp1

Select allOpen in new window

Thank you for the clarification.