ranking for multiple columns

[bascy]

A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly.

So the result should be something like this:
[tt]
ID Amount1 Ranking1 Amount2 Ranking2
---- -------- --------- ------- --------
135 123.34 4 23.00 3
184 160.23 1 60.89 2
845 140.22 3 100.20 1
987 155.00 2 1.20 4
[/tt]
The ID isnt important here.

The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one.

Is there a way to get the ranking filled in the same pass as the amounts are calculated?



Bascy
Software developer at

http://www.cbis.nl

 

[OU18]

I have a similar situation where I have multiple rows of geographical regions with each having multiple vendors (Columns) that offer differing rates. I need to rank each vendor by their rate. I have found several entries in the forums on ranking, and they do work, but they take a long time to complete if the table is large. In my case I have 401,000 rows and 13 vendors. So my file would 5.2 million rows.

I will watch this entry to see if anyone responds with how to rank across columns per row because it would be extremely helpful.

I know there is a way to do it in excel that works very nicely. But in my case, I have to many records for excel to handle.


Thanks
Steve

 

[azzazzello]

Do you need to run this query often? If not (say, once a month), then your temp table solution is fine. If you need to do it often, perhaps you can create rank columns and populate them, say, in the off hours with the temp table query, and then have it cached for the day.

 

[TonyGroves]

If you're using MySQL 4.1 or later, you could use something like:
[tt]
SET @r1=0;
SET @r2=0;
SELECT *
FROM
(
SELECT *,@r2:=@r2+1 ranking2
FROM
(
SELECT id,amount1,@r1:=@r1+1 ranking1,amount2
FROM t
ORDER BY amount1 DESC
)
q1
ORDER BY amount2 DESC
)
q2
ORDER BY id
[/tt]

 

[TonyGroves]

Ha ha! I was answering the original post, which I now notice is dated November 2003, and presumably forgotten by the poster. However, the reply should be relevant to OU18's problem as well.