0

I have the following mySQL query which I want to give the rows sorted on a calculated field with the corresponding row position. I've used OMG Ponies code from here as a template but the rows are coming back incorrectly numbered (they are being numbered in key (gemid) order without the sort). I know it has to do with the JOIN on a GROUP BY but I know know how to fix it. Thanks in advance.

SELECT g.gemid, sum_rating, @rownum := @rownum + 1 AS row_num FROM gems g
    LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from gemrating GROUP BY gemid) rt ON g.gemid = rt.gemid 
    JOIN (SELECT @rownum := 0) Z 
    WHERE g.grade = '8'  
    ORDER BY sum_rating asc

The output should come back looking like:

gemid    sum_rating    row_num
------   ------------  ----------
2           10           1
4           25           2
1           40           3
3           41           4

Instead it is coming back:

gemid    sum_rating    row_num
------   ------------  ----------
2           10           2
4           25           4
1           40           1
3           41           3
Community
  • 1
  • 1
mseifert
  • 5,390
  • 9
  • 38
  • 100
  • 1
    What is your expected output? What exactly do you want to order? – Mosty Mostacho Nov 01 '13 at 06:09
  • Sorry if I wasn't clear. The output is sorted by sum_rating order. row_num should be numbered from 1 to whatever sorted in that order. Instead it is coming back numbered in natural key order and so displays row_num as 4,5,1,6 etc. I've update the post to show better. – mseifert Nov 01 '13 at 06:12

1 Answers1

0

Looks like this works. I knew I had to sort the records first before numbering them, and then SELECT from the ordered list.

SELECT g2.gemid, g2.sum_rating, @rownum := @rownum + 1 AS row_num FROM 

(SELECT g.gemid, rt.sum_rating, g.grade FROM gems g
    LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from gemrating GROUP BY gemid) rt ON g.gemid = rt.gemid 
 WHERE g.grade = '8'  ) g2

    JOIN (SELECT @rownum := 0) Z 
    WHERE g2.grade = '8'  
    ORDER BY sum_rating asc
mseifert
  • 5,390
  • 9
  • 38
  • 100