dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 16, 1999

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


How to find the top percentage of users

Question:
I want to find out the top 5%, 10%, and 30%
customers based on their purchases. For example,
my table looks like: CustomerID Purchase(in $)
1 100
2 500
Up to 100 customers. I am getting the top 5% through:
select top 5 percent Purchase,CustomerID
from Customer
order by CustomerID desc

The same is true for the top 10%. I am getting 10 records
(assuming I have a total of 100 records). But I don't want to see the first 5 customers,
whom I have already seen as a result of the previous
query. Is there any way I can write code
in order to see only next five records?

Answer:
The strategy is to cull from the data the records you
already saw (I stuck them in a temp table called #keeptrack)
and eliminate subsequent reads by using NOT IN the
next time around like this:


if exists (select * from sysobjects where type = "U" and name like
"#keeptrack") begin drop table #keeptrack end go set rowcount 5 --create table #keeptrack(au_lname varchar(40),
title varchar(80)) go insert into #keeptrack select top 5 percent au_lname
, title from titleview --declare @reccount int select au_lname, title from titleview where title
not in (select title from #keeptrack) order by au_lname
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date