Try

SELECT Week, Sku, Store FROM MySQLTable t1 
	WHERE NOT EXISTS ( SELECT * FROM MySQLTable t2
		WHERE t2.Sku = t1.Sku 
			AND t2.Store = t1.Store 
			AND t2.TotalSalesUnits > t1.TotalSalesUnits)

>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?