Hi Naomi. Thanks for replying with a solution.
... I have not used a few of the constructs you have suggested so will need to read up/ experiment to understand exactly whats going on.

regards,
Gerard



>>When I use Group By in T-Sql, I have to include ' 'ALL' the fields I am grouping by. This has got to be a real pain.
>>Is there any Corresponding 'Set Enginebehavior 70' in SQL to overcome this problem.
>>I presume it also takes a lot longer to group By 10 fields (which might be 300 characters wide if I include Decriptions) as opposed to only being possibly 20 characters (Or better still 2 numeric fields )
>>e.g.
>>Select MyAccountNum,MyInventoryNum,MyAccountName,MyInventoryName, Sum(MyValue) as MySum
>> from MyTable
>>Group By MyAccountNum,MyInventoryNum,MyAccountName,MyInventoryName
>>
>>In above case, I really only want to Group By 2 Integer Values (Which should be extrememly Fast) but I am forced to group by
>> 2 Integer values and 2 Character Fields (each of say 100 characters each). This gets a lot worse if ther are numerous description fields.
>>
>>In that the original table will always have a one to one Number and Description combination, after the grouping it does not matter which descriptions I end up with from the original tables.
>>
>>Tia
>>Gerard
>

>;with cte as (select *, row_number() over (partition by MyAccountNum, MyInventoryNum order by MyAccountName) as Rn,
>SUM(MyValue) over (partition by MyAccountNum, MyInventoryNum) as MySum from MyTable)
>
>select * from cte where Rn = 1