Implement Ranking in MySql

Hi falks,

Recently i had a task to create a procedure to return certain data set and to fulfill the task as exactly as client wants, need to have rank to the data set. I have data related to the room prices in hotels and i need to get the best valued hotels (Most cheapest hotels regardless of room type). For this scenario i had to implement a rank value to the data set and then get the records which has 1 as the rank value. Mysql doesn't have inbuilt ranking functions like MsSql. Therefore i had to implement a workaround to do that. So this is what i did,

Note :- I used a temporary table to hold the data and then apply the rank to those data set and then select the relevant data. There is a limitation in mysql regarding temporary table. We cannot open a temporary table more than once in a same session. But in my solution I had to do a self join to that temporary table and i got stuck because of it. The solution i used is to have two temporary tables which held exact same data set and then join those two.

SET @sqll = CONCAT('SELECT HotelID, HotelName, Category, Slug, DisplayName, DisplayAddress, Address1, Address2, CityName, Country, RoomTypeID, PackageId, Popularity, 
BasicTotal, Discount, Total, ThumbnailPath, PicturePath, PackageName, PromotionTag, x.RANK
FROM (SELECT f.HotelID, f.HotelName, f.Category, f.Slug, f.DisplayName, f.DisplayAddress, f.Address1, f.Address2, f.CityName, f.Country, f.RoomTypeID, 
f.PackageId, f.Popularity, f.BasicTotal, f.Discount, f.Total, f.ThumbnailPath, f.PicturePath, f.PackageName, f.PromotionTag,
(SELECT 1 + COUNT(*)
FROM FinalDataSet_2 f1
WHERE f1.HotelId = f.HotelId
AND f1.Total < f.Total) RANK
FROM FinalDataSet_1 AS f) AS x
WHERE x.RANK = 1
GROUP BY HotelID, Total
ORDER BY',@v_Orderby,' LIMIT ',v_offset,', ',v_Limit);