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.

6
  • 1
    You do know that you can use a subquery in the from clause? Commented May 21, 2015 at 13:03
  • ON table2.table1_id=table2.id doesn't look right. Commented May 21, 2015 at 13:13
  • @ypercube sorry fixed now, I wrote the queries here, as I said I just want the method of doing this.
    – XkiD
    Commented May 21, 2015 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
    Commented May 21, 2015 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. Commented May 21, 2015 at 14:48

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.