Solved

Select  (10 to 25)  Percent

Posted on 2009-04-03
11
1,023 Views
Last Modified: 2012-05-06
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
Comment
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
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 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

by:ralmada
ralmada earned 50 total points
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)

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 27

Expert Comment

by:Zberteoc
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
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

by:Sharath
Sharath earned 100 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,
EmailPromotion,Phone,PasswordHash,PasswordSalt,rowguid,ModifiedDate
FROM Person.Contact ORDER BY ContactID) T1
EXCEPT
SELECT * FROM (SELECT TOP 10 PERCENT ContactID,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailAddress,
EmailPromotion,Phone,PasswordHash,PasswordSalt,rowguid,ModifiedDate
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

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

Providing that thetable has a promary key column called PK how about this:



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

Open in new window

0
 
LVL 13

Author Closing Comment

by:Corey2
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

by:Sharath
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

Open in new window

0
 
LVL 13

Author Comment

by:Corey2
ID: 24080066
Thank you for the clarification.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question