0

I am trying to figure out how I can get the rank of a row from a score I am generating from other field values.

The score query looks something like this 

SELECT ((table2.field1*0.4) + (table2.field2 * 0.2) + (table1.field1*0.7)) 'score' FROM `table1` LEFT JOIN table2 ON table2.table1_id=table1.id GROUP BY table1.table1_id ORDER BY `score` DESC

This generates my scores, they look something like this

|4363.195
|3970.4210000000003
|2781.8
|2601.2980000000002
|2508.905
|2269.829
|2204.1
|1893.367
|1789.575

I am trying to get rank of each provider in a query like this 

SELECT rank_here, table2.* FROM `table2` WHERE 1

This is just sample stuff I am just trying to figure out how to do this(this is why I have also included the score being generated from 2 tables, but it really doesn't matter it can be from a single table or even more than 2)

I researched : Get the rank of a user in a score table

But all I found is how to get the rank if I have the score in a field, and in my case I need to make it based on other field values.

Community
  • 1
XkiD
  • 3
  • 2
  • 1
    You do know that you can use a subquery in the from clause? – Colin 't Hart May 21 '15 at 13:03
  • ON table2.table1_id=table2.id doesn't look right. – ypercubeᵀᴹ May 21 '15 at 13:13
  • @ypercube sorry fixed now, I wrote the queries here, as I said I just want the method of doing this. – XkiD May 21 '15 at 14:01
  • @Colin'tHart I do know but I don't know how that would help me ? I am really a newbie in mysql sorry :( – XkiD May 21 '15 at 14:03
  • It helps because you can treat your query as the table that you "plugin" to any of the queries in the answer you quoted, eg SET @i=0; SELECT id, name, score, @i:=@i+1 AS rank FROM (<your_query>) ranking ORDER BY score DESC; to use one of the answers as an example. – Colin 't Hart May 21 '15 at 14:48
  • Or you can repeat your expression (table2.field1*0.4 + table2.field2*0.2 + table1.field1*0.7) wherever you would use the score column. (Note that you don't need any of the brackets either due to operator precedence.) – Colin 't Hart May 21 '15 at 14:51

1 Answers1

0

You want 1, 2, 3, ... tacked onto the output?

SELECT @rank := @rank + 1,
       s.*
    FROM ( SELECT @rank := 0 ) AS init
    JOIN (
        SELECT ... -- your existing SELECT
         ) s;
Rick James
  • 78,038
  • 5
  • 47
  • 113