CODEWITHSUNDEEP
mysqlsqlsubquerycorrelated-subquery
Zoon
Zoon

Reputation: 1088

Unknown column due to second-level subquery nesting

I want to retrieve a user's rank based on how many points the given user has compared to other users (simply counting users with more points than the given user).

However, with all the queries I have tried, I always end up with Column not found: users.id. From what I can read there is a limit from referencing correlated parent columns more than one level up.

Can I refactor my query, or do I really need to use SET @rownum := 0 style of queries?

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
            HAVING `all_points` > (
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`
            )
        ) `points_sub`
    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ? 
LIMIT 1

Upvotes: 4

Views: 1002

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can move your sub clause one level up, Remove having filter and use where filter

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT user_id,SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
        ) `points_sub`
        WHERE `all_points` > 
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`

    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ?
LIMIT 1

Upvotes: 2

skelwa
skelwa

Reputation: 585

I think the below query should work for you. You can pass the user_id of user whose rank you want to compute in both the arguments.

SELECT 
`users`.*, 
(
    SELECT COUNT(*) + 1 
    FROM (
        SELECT SUM(`amount`) AS `all_points` 
        FROM `points` 
        WHERE `type` = ? 
        GROUP BY `user_id` 
        HAVING `all_points` > (
            SELECT COALESCE(SUM(`amount`),0) 
            FROM `points` 
            WHERE `type` = ? and `user_id` = ?
        )
    ) `points_sub`
) as `rank` 
FROM `users` 
WHERE `users`.`id` = ? 
LIMIT 1

Upvotes: 0

Related Questions