ranking data

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!

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

Code:

---

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