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 #keeptrackendgoset rowcount 5--create table #keeptrack(au_lname varchar(40),
title varchar(80))goinsert into #keeptrack select top 5 percent au_lname
, title from titleview--declare @reccount intselect au_lname, title from titleview where title
not in (select title from #keeptrack) order by au_lname
