Get the rank of a user in a score table, but rank only if a different column matches a specific value

[nightkarnation]

Hey guys,

Here's a simplified table...

 

Name    | Score| Type    |
-------------------------------------------
Gustavo |10    | VsStory |
John    |22    | VsRandom|
Test    |3     | VsStory |

 

Here's a working query to only get the ranking of a specific user based on his score...

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score ) AS rank FROM
highscores b WHERE Name = 'Gustavo' AND Type = 'VsStory' ORDER BY rank LIMIT 1 ;

but the problem I am having is that the column called Type is not respecting the Type = 'VsStory' ... for example in this query, Gustavo gives a rank value of 2...when it should be 1... for some reason Type column is not being processed to only count the VsStory values and not all the rows disregarding if its VsStory or VsRandom...

 

Any ideas??

 

Thanks a lot in advance!

[Barand]

you need to check for same type too

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score AND a.type = b.type) AS rank
FROM highscores b
WHERE Name = 'Gustavo' AND Type = 'VsStory'
ORDER BY rank

[nightkarnation]

Hello Barand, thanks so much for your solution! understood and applied (y)

 

I need something very similar now but I can't seem to get it working...

Based on the same type of query...I need to get the ranking this time not from a Score column but instead from the total of 2 columns (Gf and Ga) / Gf - Ga = G total...

 

Name     | Score| Type          | Gf | Ga
-------------------------------------------
Gustavo |10      | VsStory     | 5   |  2
John       |22      | VsRandom| 5   |  1
Test        |3        | VsStory     | 5   |  4

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.Gf - a.Ga > b.?? AND a.type = b.type) AS rank
FROM highscores b
WHERE Name = 'Gustavo' AND Type = 'VsStory'
ORDER BY rank

I need to do Gf - Ga ... to get the highest total in this case and rank it based on that amount...

I tried several ways but cant seem to understand the logic,

 

Thanks a lot in advance!

Edited December 5, 2013 by nightkarnation

[Barand]

try this method instead

SELECT a.name, 1+COUNT(b.name) as rank
FROM highscores a
LEFT JOIN highscores b
    ON a.type = b.type AND b.Gf-b.Ga > a.Gf-a.Ga
WHERE a.type = 'VbStory'
GROUP BY a.name;

+---------+------+
| name    | rank |
+---------+------+
| Gustavo |    1 |
| Test    |    2 |
+---------+------+