CODEWITHSUNDEEP
mysqlsql
Radical_Activity
Radical_Activity

Reputation: 2738

How to add a WHERE statement correctly to a complex MySQL query?

I have a quite complex query to get some data from the database, sort them and rank them accordingly.

Here is the SQL fiddle for it: SQL Fiddle

Now what I want to do is, to add a WHERE statement to this query, so only limited users will be selected (3 users above and 3 users below, the id = 8).

WHERE sort BETWEEN @userpos - 3 AND @userpos + 3

So it should look something like this, but with the first example:

SQL Fiddle

I have already tried to implement this WHERE statement to this query, but I couldn't figure it out where should I add, as I've always received error (that the column cannot be found).

Any suggestion and / or solution for my problem? Should I rewrite the whole query for this?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you can do this with a subquery:

SET @userid = 8

SELECT *
FROM (SELECT @pos := @pos + 1 AS sort, points, r.userid, s.active
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE userid = @userid;

Note that this eliminates a layer of subqueries that you have. Otherwise, it is quite similar to your query.

EDIT:

The above was based more on the SQL Fiddle than on the question. (Oops.)

To get three rows before and after a given row is possible and just a small tweak, using a trick. The trick is to define another variable with the user pos and then use that variable in the outer query:

SELECT *
FROM (SELECT @pos := @pos + 1 AS sort, points, r.userid, s.active,
             if(userid = @userid, @userpos := @pos, 0)
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0, @userpos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE `sort` between @userpos - 3 and @userpos + 3;

Note: MySQL does not guarantee the order of evaluation for variables in the select. The following is a bit safer in terms of order of execution:

SELECT *
FROM (SELECT (case when (@pos := @pos + 1) is NULL then NULL
                   when (case when (userid = @userid) then @userpos := @pos else 1 end) is null
                   then NULL
                   else @pos
              end) AS sort, points, r.userid, s.active,
             if(userid = @userid, @userpos := @pos)
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0, @userpos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE `sort` between @userpos - 3 and @userpos + 3;

The weird case statements are to ensure statement executions. The is null is to ensure that the when clauses fail, so the assignments are made sequentially.

Upvotes: 1

Related Questions