0

I have 2 different tables in my database by the name of: rank, settings.

Here is how each table looks like with a few records in them:

Table #rank:

id  points userid
--  ----- ------
 1    500      1
 2    300      2    
 3    900      3
 4   1500      4
 5    100      5
 6    700      6
 7    230      7
 8    350      8
 9    850      9
10    150     10

Table #settings:

userid    active 
------    ------
     1         0
     2         1
     3         1
     4         1
     5         1
     6         0
     7         1
     8         1
     9         0
    10         1

I want to get the rank of a specific user by user_id from the rank table ordering by their points. Also I would Only want to include the users in the ranking results, if they have active = 1 set in the settings table.

I have a simple ranking query, but it is not really effective, because it does include everyone even if the user is not active:

SELECT * FROM 
(SELECT @sort:=@sort+1 AS sort, points, userid
 FROM rank,
(SELECT @sort := 0) s 
 ORDER BY points DESC) t 
WHERE userid= 8

Any idea, how could I achieve my goals here?

Radical_Activity
  • 2,618
  • 10
  • 38
  • 70

2 Answers2

1

Few sub queries. First gets all the users who are active in the right order. That is used as a source for another query to add the rank. Then this is used as the source for the points and rank for the userid you are actually interested in

SELECT sort, points
FROM
(
    SELECT @sort:=@sort + 1 AS sort, points, userid
    FROM
    (
        SELECT rank.points, rank.userid
        FROM rank
        INNER JOIN settings
        ON rank.userid = settings.userid
        WHERE settings.active = 1
        ORDER BY points DESC
    ) sub0
    CROSS JOIN (SELECT @sort:=0) sub2
) sub1
WHERE sub1.userid = 8
Kickstart
  • 21,403
  • 2
  • 21
  • 33
0

Borrowing the idea from: https://stackoverflow.com/a/4474389/92063

SELECT 
    @rn:=@rn+1 AS RANK
    ,USER_ID
    ,POINTS
FROM (
    SELECT
        R.userid AS USER_ID
        ,R.points AS POINTS
    FROM
        rank R
    INNER JOIN
        settings S
        ON R.userid = S.userid
    WHERE
        S.active = 1
    ORDER BY
        R.points DESC
) t1, (SELECT @rn:=0) t2;
Community
  • 1
  • 1
heikkim
  • 2,955
  • 2
  • 24
  • 34