sort and rank all in one query
Posted: Mon Mar 07, 2005 3:08 pm
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:
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
Code: Select all
SET @rank=0;
(SELECT @rank:=@rank+1 `ranker`, RankByMe, SortByMe
FROM table
WHERE Column3 = 'Look'
ORDER BY RankByMe)
ORDER BY SortByMe;edit: oh, yea and even if i use the same column for the inner and outer columns, I get funked up numbers