ranking data

[mydigits]

I have a select statement which is intended to output rows ranked by totalsales for a given area which goes like this:

SELECT A.name, A.district, A.month, A.year, A.totalsales,
(SELECT COUNT(DISTINCT totalsales)
FROM mreport B
WHERE B.year = 2003
AND B.month = 1
AND B.totalsales >= A.totalsales
AND B.district = A.district) AS rank
FROM mreport A
WHERE A.district = 'manila'
AND A.totalsales <> 0
AND A.year = 2003
AND A.month = 1
ORDER BY rank, name

The result returns NULL values in rank except the first row which has a value of 1. Also, the query should handle ties in its rank values. Oracle has no problem with this. What's wrong with my statment? How will I implement this in MySQL? BTW, im using MySQL 4.1 which supports subqueries.

Thanks and more power!

[nicc777]

Don't take it personally, but I hate nested SELECT's. Try this:

SELECT 
  DISTINCT name, district, month, year, COUNT(totalsales) AS tsales
FROM 
  mreport 
WHERE 
  year = '2003' AND 
  month = '1'   AND 
  totalsales <> 0  
GROUP BY 1 
ORDER BY 5 DESC 
LIMIT 0,10

This should give you the top 10.

I also assumed from you given query that all the data is in one table: `mreport`.

Hope this helps. PS: I have not tested the SQL, so you might have to tweak it.

Cheers