Quick question about MAX

[kevinfwb]

It's been a little bit since my SQL class so please bear with me 

 

I have a table that has a list of store numbers and their sales.  I'm trying to pull the max sales and the corresponding store number.

 

I can SELECT MAX(sales) from Sales and I get the highest value.  However when I select storeNum, MAX(sales) FROM Sales I get the highest value of each pair, which is all of them. 

 

I know it has something to do with a subquery, but I don't remember the exact syntax.  I've also tried..

 

SELECT storeNum, sales FROM Sales WHERE sales=(select MAX(sales))

This query runs, I just get no results.

 

Any help is greatly appreciated.

 

-Kevin

 

**UPDATE**

 

I think I have solved my own problem.  In my originaly query, I have a where clause (WHERE WEEK = 2 AND YEAR = 2007)  I did not have that same WHERE clause in my subquery.  After adding the WHERE clause to my subquery, it seems to be working.

[Barand]

SELECT storeNum, SUM(sales) as totalSales

FROM sales

GROUP BY storeNum

ORDER BY totalSales DESC

LIMIT 1