Get the rank of a row based on a score I am generating from other field values

Question

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.

Answer 1

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;