Search code examples
sqlsubqueryrankingrankrow-number

Ranking players in SQL database will return always 1 when where id_user is used

I basically have a table "race" with columns for "id_race", "id_user" and columns for user predictions "pole_position", "1st", "2nd", "3rd" and "fastest_lap". In addition to those columns, each prediction column also has a control column such as "PPC", "1eC", "2eC", "3eC" and "srC". Those control columns are then compared by a query against a "result" table. Then the control columns in race are awarded points for a correct prediction.

table race

I want to add up those results per user and then rank them per user. I want to show that rank on the player's user page. I have a query for my SQL which works fine in itself and gives me a list with rank column.

SELECT 
    @rownum := @rownum +1 AS rank, 
    total, 
    id_user 
FROM 
    (SELECT 
         SUM(PPC + 1eC + 2eC + 3eC + srC ) AS total, 
         id_user 
     FROM 
         race 
     GROUP BY 
         id_user 
     ORDER BY 
         total DESC) T, 
    (SELECT @rownum := 0) a;

Output of rank query:

Output

However when I add the where id_user it always gets the first rank. Does anyone have an idea if this can be solved and how I could achieve it to add where to my rank query?

I've already tried filtering. In addition, I have tried to use the Row_number function. It also always gives a result of 1 because only 1 user remains after filtering. I am unable to filter out the correct position. So please help!

Solution

  • You have to create a view to extracting the correct rank. Once you use WHERE clause, you will get the rank based on the population rather that the subset.

    Please find an indicative answer on fiddle where a CTE and ROW function are used. The indicative code is:

    WITH sum_cte AS (
  SELECT ROW_NUMBER() OVER(ORDER BY SUM(PPC + 1EC + 2eC + 3eC + srC) DESC) AS Row, 
    id_user, 
    SUM(PPC + 1EC + 2eC + 3eC + srC) AS total_sum
  FROM race
  GROUP BY id_user)
SELECT Row, id_user, total_sum
FROM sum_cte
WHERE id_user = 1

    User 1 with the second score will appear with a row valuation 2.