Solved

Select  (10 to 25)  Percent

Posted on 2009-04-03
11
1,018 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 26

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 40

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 26

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 40

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 26

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 40

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 48
Sql Data via Excel--performance issues 2 55
Truncate vs Delete 63 105
Grid querry results 41 77
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now