sql запрос

--create table sales
--(
-- dep nvarchar(50),
-- art nvarchar(50),
-- price money,
-- qty numeric(10,4)
--)

--go

--insert into sales values ('o1', 'a1', 11, 5)
--insert into sales values ('o2', 'a2', 22, 4)
--insert into sales values ('o3', 'a3', 33, 3)
--insert into sales values ('o1', 'a4', 44, 2)
--insert into sales values ('o2', 'a5', 55, 4)
--insert into sales values ('o1', 'a1', 22, 5)

select
	totalSold.dep, 
	totalSold.art,
	cast(maxSoldByDepArt.maxSold as numeric(10,4)) as maxSold
from 
	(select 
		dep, 
		max(totalSold) as maxSold
	from
		(select 
			dep, 
			art, 
			sum(price * qty) as totalSold 
		from sales
		group by dep, art
		) totalSold
	group by dep
	) maxSoldByDepArt

inner join 
	(select 
		dep, 
		art, 
		sum(price * qty) as totalSold 
	from sales
	group by dep, art
	) totalSold
on totalSold.dep = maxSoldByDepArt.dep
and totalSold.totalSold = maxSoldByDepArt.maxSold

select top 1 WITH TIES sum(price * qty) as sm, dep, art
  from #sales 
  group by dep, art
  order by row_number() over(partition by dep order by sum(price * qty) desc)