I am looking to select the top % from a subquery based on
grouped values. For example, I want to select the top 10%
of customers living in each state sorted by income in
descending order. The only way I could think to do it was
to put them in a temp table and run a separate query to
append every 10%. I am sure there is a better way to

Jeremy,

Pls post some table structure/sample data for correct answer. I've created a sample table
#rank in my sample example and populated records

--sample table and data.

create table #rank(customerid nvarchar(5),
state nvarchar(15),
income decimal (10,2))

insert into #rank
select 'ERNSH','Austria',81.9100 union all
select 'ERNSH','Austria',140.5100 union all
select 'ERNSH','Austria',146.0600 union all
select 'ERNSH','Austria',3.6700 union all
select 'ERNSH','Austria',208.5800 union all
select 'John','USA',8231.9100 union all
select 'John','USA',140.5100 union all
select 'John','USA',14634.0600 union all
select 'John','USA',32.6700 union all
select 'John','USA',2343408.5800

--query to get top 10 percent for each customerid and state would be as follows.

select x.customerid, x.state, x.income
from
(select a.customerid, a.state, a.income,count(distinct b.income) rnk
from #rank b, #rank a
where a.income <= b.income and
a.customerid = b.customerid and
a.state = b.state
group by a.customerid, a.state, a.income) X,
(select customerid, state, ceiling(count(*) * 10.00/100) top_cnt --(A): TOP Clause
from #rank
group by customerid, state) Y
where x.customerid = y.customerid and
x.state=y.state and
x.rnk <= y.top_cnt


In the above query remember the clause (A) which acts as a "top X percent" . So if you
want to retrieve top 20 percent change that clause as follows:

select customerid, state, ceiling(count(*) * 20.00/100) top_cnt --(A): TOP Clause


--
-Vishal