>I have a SQL which is using two calculated fields with the same conditions. It goes like this:
>
>

>SELECT Member.FirstName,Member.LastName,
> (SELECT MAX(Order.AddDate) FROM Order WHERE Order.NoMember=Member.Numero) AS AddDate,
> (SELECT COUNT(*) FROM Order WHERE Order.NoMember=Member.Numero) AS CountRecord
> FROM Member
>

>
>So, basically, as you can see, the two calculated fields are using the same conditions. I was trying to know if that could simplified in order to avoid going into the Order table twice.

Try using derived table - it will be most likely a better performance:

select M.FirstName, M.LastName, O.AddDate, O.CountRecord from Member M
inner join (select max(AddDate) as AddDate, count(*) as CountRecord, NoMember 
from [Order] group by NoMember) O
on M.Numero = O.NoMember