Question 20300 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: When adding a WHERE, the position is always 1

I wrote a SQL query that outputs a list of clan ids and their positions.

SET @n := 0;

SELECT id, @n := @n + 1 AS position
FROM (
    SELECT clans.id, SUM(clans_members.xp) AS xps
    FROM clans
    INNER JOIN clans_members ON clans_members.clans_id = clans.id
    GROUP BY clans.id
    ORDER BY xps DESC
) AS s;

Result:

Result SQL

But if I add WHERE id = 6 (as seen in the previous screenshot, the position of the clan with id 6 is 3), it outputs this result:

Result with WHERE

Even if I specify any other id, it will still output position 1. What to do and how to fix it?

Tags:

2

Answers


  1. 0

    You should use HAVING clause instead of WHERE clause in your case.

    SELECT 
     clans.id, SUM(clans_members.xp) AS xsp, (@n := @n + 1) AS position
FROM clans
     INNER JOIN clans_members ON clans_members.clans_id = clans.id 
     CROSS JOIN (SELECT @n := 0) param
GROUP BY clans.id
HAVING clans.id = 6
ORDER BY xsp DESC;
    Login or Signup to reply.
  2. 0

    Since you’re using MySQL v8, you can do something like this:

    WITH cte AS (
  SELECT clans.id, SUM(clans_members.xp) AS xps, 
         ROW_NUMBER() OVER (ORDER BY SUM(clans_members.xp) DESC) RowNum
    FROM clans
    INNER JOIN clans_members ON clans_members.clans_id = clans.id
    GROUP BY clans.id
    ORDER BY xps DESC)
SELECT id, RowNum
FROM cte
WHERE id=6;

    Use ROW_NUMBER() OVER () to generate row number on your original query, wrap that in a common table expression(cte) then filter the id=6.

    Demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search