My SQL table is the following...
Sku Store Week TotalSalesUnits
-------- ------ ----- ---------------
3763610 001 40 2
3763610 001 41 5
3763610 001 42 3
3763610 002 40 1
3763610 002 41 3
3763610 002 42 2
3763610 003 40 4
3763610 003 41 2
3763610 003 42 3

In my SELECT list, I need to get the week number with the maximum total sales units, something like this...

SELECT [list of other fields], WeekNumber =
(select top 1 Week, max(TotalSalesUnits) maxSalesUnits
from MySQLTable where Sku = '3763610' and Store = '001')
FROM [AnotherSQLTable]

But the problem is the inner SELECT return 2 values. How can I get my value (in that case must be 5)... in one shot... I mean without use of a temporary table?