Search code examples
mysqlrownum

MySql : How to use rownumber with a join Query statement?

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

enter image description here

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.

Solution

  • Use a subquery:

    SELECT
    @rownum := @rownum + 1 AS rownum,
    T1.*
FROM
(
    SELECT
        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
    GROUP BY dd.Device_Model
) AS T1, (SELECT @rownum := 0) AS r
ORDER BY Touch DESC