Page 1 of 1

sort and rank all in one query

Posted: Mon Mar 07, 2005 3:08 pm
by magicrobotmonkey
Ok, I have this problem where I need to rank data by one column and sort it by another. I am trying to do this all in one query without using a temp table or sorting any arrays. So what I am doing is something like this:

Code: Select all

SET @rank=0;
(SELECT @rank:=@rank+1 `ranker`, RankByMe, SortByMe
   FROM table
    WHERE Column3 = 'Look'
    ORDER BY RankByMe)
ORDER BY SortByMe;
reading the documentation over at mysql, I learned that I could union one query and in so doing, essentially sort it twice. The sorting seems to work ok. The ranking bit works ok when I remove the outer orderby, but with it in place, it fails in a strange way I can't quite figure out. The ranlking numbers seem to come out randomly. I mean they are always in the same strange order, but I can't figure out why they are coming out in the order they are.

edit: oh, yea and even if i use the same column for the inner and outer columns, I get funked up numbers