Rank variable value in MySQL is always being set to null

Question

In a sample procedure I am trying to find the rank the of the cities according to the number of offices. I have a Location Table and an Office Table and a location can have multiple offices ( In my case I am assuming Boston has 3 offices and Bangalore has only 2) offices and I want them to rank accordingly . I am using the below SP but it returns me the rank as 0 and 0 and also MySQL query browser is being crashed . I guess I am making some error here .

   BEGIN

   DECLARE rank INT DEFAULT 0;

   SELECT rank =rank+1 AS Rank,l.Location_Name,COUNT(*)
   AS Number_Of_Offices FROM employee_leaves_management_system.location l
   INNER JOIN
   employee_leaves_management_system.office o
   ON l.Location_ID = o.Office_Location_ID
   GROUP BY l.Location_Name
   ORDER BY Number_Of_Offices;

   END $$

A quick help will be much appreciated !!

Answer 1

= is the assignement operator only for SET statements,in your case is a comparison operator,for SELECT use rank := rank+1

Example

SET @var=1;
SELECT @var = 1 + 1; -> This compares @var which is one to 2 so it returns 0.

Result 0

SET @var=1;
SELECT @var := 1 + 1; -> This actually adds up.

Result 2

Answer 2

Hi and welcome to the forums. In future could you please provide DDL (run the SHOW CREATE TABLE employee_leaves_management_system.location\G (&c.) and also DML (INSERT INTO employee_leaves_management_system.location VALUES(...)) so that those who wish to help you can easily test solutions. See here (and links within) on useful tips on asking questions.

As to your question, you could take a look here for a pure SQL solution to a ranking query (the OP's original SQL is actually correct for his problem, can't tell if it will be for yours).