Help with MAX function

[kevinfwb]

I have a MySQL database for a chain of restaurants that tracks store sales, among other things.

I have a table called sales that stores

 

storeNum

period

week

year

sales

 

I'm trying to find which store has the max sales for a given period/week/year.

storeNum, period, week and year are all INTS and sales is a double

I have sales set to a double because other fields stores/calculates percentage of increase/decrease etc to the 4th decimal.

 

I can get the code to work using

SELECT MAX(sales) FROM Sales WHERE period=8 AND week=2 AND year=2007

 

However, this doesn't give me the corresponding store number so I tried the following code that returns 0 results.

SELECT sales, storeNum FROM Sales WHERE period=8 AND week=2 AND year=2007 AND sales=(SELECT MAX(sales) FROM Sales)

 

The code doesn't pull any rows for any period, week, or year.

I think I've tried this code in just about every imaginable syntax.  Any help is greatly appreciated.

 

-Kevin

 

[Illusion]

why u need a subquery , insted of that u can do

 

SELECT sales, storeNum ,MAX(sales) FROM Sales WHERE period=8 AND week=2 AND year=2007;

 

 

[Barand]

SELECT sales, storeNum

FROM Sales

WHERE period=8 AND week=2 AND year=2007

ORDER BY sales DESC

LIMIT 1