Problem with Having and Max

Hello,
I have the following query:
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
Group By
Code,
TestNo
Having
TestNo = Max(TestNo)
Order By
Code
What I want to achieve is summing only the weight for Max(TestNo) for each
code. But since I have to group by TestNo to use it in the Having clause it
is giving me the sum for all TestNo for each Code.
How can I achieve summing only the weight for Max(TestNo) for each code?
Thanks.
MichelTry,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Michel Hardy" wrote:

> Hello,
> I have the following query:
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> Group By
> Code,
> TestNo
> Having
> TestNo = Max(TestNo)
> Order By
> Code
> What I want to achieve is summing only the weight for Max(TestNo) for each
> code. But since I have to group by TestNo to use it in the Having clause i
t
> is giving me the sum for all TestNo for each Code.
> How can I achieve summing only the weight for Max(TestNo) for each code?
> Thanks.
> Michel|||Correction,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
and TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
> #tWidra.Code)
> Group By
> Code,
> TestNo
> Order By
> Code
>
> AMB
> "Michel Hardy" wrote:
>