Do you want to get total value for a customer then?
Try
;with cte as (select Area, Customer, sum(Value) as TotalValue from CustomerInfo GROUP BY Area, Customer),
cte1 AS (select *, row_Number() over (partition by Area order by [TotalValue] DESC) as [Row] FROM cte)
select * from cte1 where Row <=20
>Hi Naomi. Thanks for replying.
>
>That nearly works,
>I get Top 20 ok but the Customer Number duplicates
>
>My Table would not ncessarily have unique occurences i.e. there could be multiple occurences of the same Area and Customer
>Would that be the reason and would I also need to do some sort of Grouping
>
>Regards,
>Gerard
>
>
>
>>>I have a table like:
>>>Area, Customer,Value
>>>
>>>Is there one Sql command which will select the Top 20 Cusotmers, By Value in each Area ?
>>>Tia
>>>Gerard
>>
>>SQL 2005+
>>
>>;with cte as (select *, row_Number() over (partition by Area order by [Value] DESC) as [Row] FROM CustomersInfo)
>>
>>select * from cte where Row <=20
If it's not broken, fix it until it is.
My Blog