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