Posted by Alexander Kuznetsov on 10/01/42 11:27

it would be a snap in 2005, yet it's quite doable in 2000

create table #zips(id int, region char(2), zip int, sum_sales int)
insert into #zips values(1, 'IL', 60563, 12)
insert into #zips values(2, 'IL', 60564, 13)
--- a tie deliberately
insert into #zips values(3, 'IL', 60565, 14)
insert into #zips values(4, 'IL', 60566, 14)
insert into #zips values(5, 'IL', 60567, 14)
insert into #zips values(6, 'IL', 60569, 14)

insert into #zips values(7, 'WI', 53718, 12)
insert into #zips values(8, 'WI', 53711, 1)
insert into #zips values(9, 'WI', 53712, 4)
insert into #zips values(10, 'WI', 53715, 7)
insert into #zips values(11, 'WI', 53714, 5)
insert into #zips values(12, 'WI', 53712, 3)

select * from #zips z
where (select count(*) from #zips z1 where z.region=z1.region
and ((z.sum_sales<z1.sum_sales)or(z.sum_sales=z1.sum_sales and
z.id<=z1.id))) <= 3

id region zip sum_sales
----------- ------ ----------- -----------
4 IL 60566 14
5 IL 60567 14
6 IL 60569 14
7 WI 53718 12
10 WI 53715 7
11 WI 53714 5

(6 row(s) affected)

drop table #zips

[Back to original message]