Solved

# Select  (10 to 25)  Percent

Posted on 2009-04-03
Medium Priority
1,028 Views
Is there a syntax to be able to select records between 2 values.

Select Between 10 to 25 percent * from Table1

So if I have a table with 100 rows it would return rows

10 through 25

Similar to Select top 10 percent but with a range.
0
Question by:Corey2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +3

LVL 27

Accepted Solution

Chris Luttrell earned 1000 total points
ID: 24065170
are they orderd?  If so and not a bad query you could try
select top 25 * from table1 order by something
except
select top 10 * from table1 order by something
0

LVL 41

Assisted Solution

ID: 24065175
You can try something like this
``````SELECT field1, field2, field3 from Table1
WHERE field1 IN
(SELECT TOP 25 PERCENT field1 FROM table1)
AND field1 NOT IN
(SELECT TOP 10 PERCENT field1 FROM table1)
``````
0

LVL 75

Assisted Solution

Anthony Perkins earned 200 total points
ID: 24065504
Or you can use a CTE as in (using the Products table in the Northwinds database as an example):

WITH ProductsCTE (RowNumber, ProductID, ProductName) As
(
Select      TOP 25 PERCENT
ROW_NUMBER() OVER (ORDER BY ProductID) RowNumber,
ProductID,
ProductName
From      Products
)

Select      ProductID,
ProductName
From      ProductsCTE
Where      RowNumber >= (Select MAX(RowNumber) From ProductsCTE) * .1
0

LVL 27

Expert Comment

ID: 24065783
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
``````
0

LVL 41

Expert Comment

ID: 24069195

``````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
``````
0

LVL 27

Expert Comment

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

LVL 41

Assisted Solution

Sharath earned 400 total points
ID: 24069472

Well, I will explain this with an example. Lets take the table Person.Contact in AdventureWorks database.
SELECT COUNT(*) FROM Person.Contact
19972 records are there in this table. The asker wants records in the range of 10 to 25 percent.
I assume that he wants the records in the range 10-25 percent in the order of primary key column.
SELECT TOP 25 PERCENT * FROM Person.Contact ORDER BY ContactID
There are 4993 records for this query with maximum ContactID as 4998. ContactID is the primary key for this table.
SELECT TOP 10 PERCENT * FROM Person.Contact ORDER BY ContactID
For the above query, there are 1998 records with maximum COntactID as 2003.
As the asker wants the records in the range 10-25 percent, lets try like this.
SELECT ContactID FROM (SELECT TOP 25 PERCENT * FROM Person.Contact ORDER BY ContactID) T1
EXCEPT
SELECT ContactID FROM (SELECT TOP 10 PERCENT * FROM Person.Contact ORDER BY ContactID) T2
This will give you the contactIDs from 2004 to 4998. Ideally the asker wants the records with these ContactIDs.
Note: You cannot use EXCEPT if you have xml datatype column in your table.
I used ROW_NUMBER function to achieve this.
SELECT *,ROW_NUMBER() OVER(ORDER BY ContactID) RN FROM Person.Contact
The above query will assign a Row_Number for each record in the order of ContactID.
SELECT (SELECT COUNT(*) FROM Person.Contact)/10,(SELECT COUNT(*) FROM Person.Contact)/4
The above query values 1997 and 4993.
So the asker wants the records between 1997 and 4993 (Observe that these are row numbers and not contact ids)
SELECT RN,*
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY ContactID) RN FROM Person.Contact) Tab
WHERE RN BETWEEN (SELECT COUNT(*) FROM Person.Contact)*0.1 AND (SELECT COUNT(*) FROM Person.Contact)/4.0
We got the recrds from ContactID from 2003 to 4998.
Slightly different from the EXCEPT query. We got one extra record.
The asker can use any of these two queries.
SELECT * FROM (SELECT TOP 25 PERCENT ContactID,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailAddress,
FROM Person.Contact ORDER BY ContactID) T1
EXCEPT
SELECT * FROM (SELECT TOP 10 PERCENT ContactID,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailAddress,
FROM Person.Contact ORDER BY ContactID) T2
or
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY ContactID) RN FROM Person.Contact) Tab
WHERE RN BETWEEN (SELECT COUNT(*) FROM Person.Contact)*0.1 AND (SELECT COUNT(*) FROM Person.Contact)/4.0
Hope you understand this.
Asker - Correct me if my undrstanding of your requirement is wrong.
0

LVL 27

Assisted Solution

Zberteoc earned 200 total points
ID: 24077882
Thanks Sarath for your exhaustive description.

``````select top 25 percent
*
from
YourTable
where
PK not in ( select top 10 percent PK from YourTable order by PK )
order by
PK
``````
0

LVL 13

Author Closing Comment

ID: 31566478
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
0

LVL 41

Expert Comment

ID: 24080016

>> 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
``````
0

LVL 13

Author Comment

ID: 24080066
Thank you for the clarification.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durinâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If youâ€™ve ever visited a web page and noticed a cool font that you really liked the look of, but couldnâ€™t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yoâ€¦
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll