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


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.

  • 1
    You do know that you can use a subquery in the from clause? May 21 '15 at 13:03
  • ON table2.table1_id=table2.id doesn't look right. 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. May 21 '15 at 14:48

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

SELECT @rank := @rank + 1,
    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, privacy policy and cookie policy

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