I hava a MySQL Statement which joins 2 tables and shows the result in Desc order of field 'touch'. This is my SQL Statement
SELECT @rownum := @rownum +1 rownum,
(IF( dm.Brand_FULL = '1', dd.Device_Brand, dm.Brand_Full )) AS Brand,
dd.Device_Model AS Model,
CONCAT( dm.Service_provider, ' ', dm.Model_Full ) AS 'Marketing Name',
max( Touch_Count ) AS Touch FROM Device_Details dd
JOIN Device_Models dm ON dd.Device_Model = dm.Model_Short,
(SELECT @rownum :=0 )r GROUP BY dd.Device_Model ORDER BY Touch_Count DESC
This give me a result like this
The result is perferct, and its based on the descending order of Touch field.Now the problem is that the rownum is not in order. So how to modify the mySQL statement so that i can get the rownum in order without changing the DESC order of Touch field.
Use a subquery: