Top 3 sales for each distributor (Urgent)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Top 3 sales for each distributor (Urgent)

Author

Topic

new_raju
Starting Member

1 Post

Posted - 2005-03-24 : 05:46:13

Dear all

I am having table

Table name is dist_sales

Dis_id Item_no sales_qty
10 i1 10
10 i2 23
10 i3 05
10 i4 08
20 i1 04
20 i2 06
20 i3 29
20 i4 10
20 i5 55
30 i1 09
30 i2 07
30 i3 77
30 i4 20
ETC...........

Like this is there are many distributor...

I want get top three sales for each distributor in a single query in sql server

select top 3 Dis_id ,Item_no,sales_qty from dist_sales
where Dis_id=10 order by sales_qty desc

Above query will work fine only for one distributor id.

I want the query for the distributors in single query

Please help me. This is very urgent

Thanks in Advance
Raju

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-24 : 06:06:39

declare SelectTop3 Cursor
for
select distinct Dis_id from dist_sales
Open SelectTop3
Declare @no nvarchar(2000)
fetch next from SelectTop3 into @no
while @@Fetch_Status<>-1
begin
set rowcount 3
select * from dist_sales where dis_id=@no
fetch next from SelectTop3 into @no
end
Deallocate SelectTop3

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-24 : 06:16:46

no cursors, please


Select 	Dis_id, Item_no, sales_qty
From 	dist_sales as t1
Where 	(Select count(distinct Item_no) from dist_sales Where Dis_id = t1.Dis_id and Item_no >= t1.Item_no)<=3
Order By Dis_id, Item_no, sales_qty

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-24 : 06:36:08

Nice coding Mladen

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources