I'm trying to get the row position from a grouped query.
SELECT x.id, x.position, x.name, x.total FROM (
SELECT u.*, count(invites.user_id) as total, @rownum := @rownum + 1 AS position
FROM users u JOIN (SELECT @rownum := 0) r
LEFT JOIN invites i ON i.user_id = u.id AND i.status = 2
GROUP BY u.id
ORDER BY total DESC
) x
WHERE x.id = 2004
But my rank is getting out wrong. The rownum is not counting correctly the position of the row. Maybe something about the join and group?
Example of multiple selects, with some ids, on a table:
id total position
10 15 250
158 13 124
3 10 4
(...)
The error here is that id = 3 shoud be on the 3rd position and not on the 4th. And the id = 10 should be 1st, and not on 250th position, and so on..
A better example:
This is too long for a comment. The problem in the sample Fiddle is that the sort is not stable. There are multiple values where the total is 1, and these can be ordered in an arbitrary way. And, they clearly are.
You can introduce a stable sort by including an additional column in the sort to uniquely identify each row. For the SQL Fiddle example, the queries would be:
SELECT `rank`
FROM (select @rownum:=@rownum+1 `rank`, p.names, count(p.names) as total
from TableName p, (SELECT @rownum:=0) r
group by names
order by total, p.names DESC
) s
WHERE names = 'ship';
SELECT p.names, count(p.names) as total
FROM TableName P
GROUP BY names
ORDER BY total desc, p.names;
EDIT:
I see the problem. Using an additional layer of subqueries seems to fix the problem:
select n.*
from (SELECT @rownum:=@rownum+1 as `rank`, n.*
FROM (select p.names, count(p.names) as total
from TableName p cross join (SELECT @rownum:=0) r
group by names
) n
order by total desc, names
) n
where names = 'mango';
(SQL Fiddle is here.)
This is very curious behavior and don't understand it. I wonder if it is related to the implicit ordering after a group by.