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


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

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
Thanks for your registration, follow us on our social networks to keep up-to-date