2

I am trying to compute the ranks for a MySQL table, ranking by a table colum that also contains NULL value. I found the snippet from this questions's answer, and adapted it to my use case, but it doesn't work - it seems that the @rownum := @rownum + 1 as rank is computer prior to ordering by order by.

My question is: why does this happen? Is doing the variable increment before the order by standard MySQL behavior? does it have anything to do with the NULLs (should't they just get ordered last and not affect anything else?).

Now, I have already solved my rank computing problem with a subquery in the SELECT clause, but I was hoping that this method with the variable would be more efficient because there is no subquery involved, and I also want to get a better understanding of how variables in queries work.

This is an example query (note: global_average_rating_overall also contains NULLs - could this be the cause for weird behavior?):

set @rownum := 0;
select
    ID,
    post_title,
    m.meta_value as global_average_rating_overall,
    @rownum := @rownum + 1 as rank
from
    wp_posts p
    left join wp_postmeta m on m.post_id = p.ID and m.meta_key = 'global_average_rating_overall'
where
    p.post_status = 'publish'
order by global_average_rating_overall desc;

The result is along the lines of:

ID  global_average_rating_overall  rank
--+------------------------------+-----
3   4                              14
9   3.9                            22
7   3.75                           11
...                                 1
...                                 2
...                                 3

(note the last ranks that don't match the ranking by global_average_rating_overall)

Community
  • 1
  • 1
NeuronQ
  • 7,527
  • 9
  • 42
  • 60
  • @juergend so the accepted answer on this question is also wrong?: http://stackoverflow.com/questions/3333665/mysql-rank-function – NeuronQ May 12 '13 at 07:41
  • Can you provide sample data? I don't see why your query should produce weird results. And it's not reproducable for me. – fancyPants May 12 '13 at 10:26
  • No response? Voting to close as too localized then. – fancyPants May 12 '13 at 18:53
  • @tombom I'll try and replicate it with another data set. sorry, I basically solved the problem with a subquery and got swamped in something else, but as soon as I get some free time I'll take another look and see if I can get to the root of this issue too... – NeuronQ May 13 '13 at 19:35

1 Answers1

0

Reading and writing to the same variable during query execution is undefined. If the sort is performed by an index the assignment may happen differently than if the sort is performed in a temporary table.

Justin Swanhart
  • 1,826
  • 13
  • 15