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