Solved

Select  (10 to 25)  Percent

Posted on 2009-04-03
11
1,014 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 26

Expert Comment

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

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 26

Expert Comment

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

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
Comment Utility
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
Comment Utility
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
Comment Utility

>> 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
Comment Utility
Thank you for the clarification.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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