For the following query:

FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u.last_activity_date > '2012-01-13 02:42:10')
AND (l.id = (
    SELECT `leads`.`id`
    FROM `leads`
    WHERE (lender_id = l.lender_id)
    AND (client_id = l.client_id)
    AND (disabled = 0)
    ORDER BY `price` DESC
    LIMIT 0, 1
GROUP BY `l`.`client_id`
ORDER BY `u`.`last_activity_date` DESC

How could we restructure the query to remove the correlated subquery?

  • which mysql version are you using? can you post the explain for that query? – golimar Apr 12 '12 at 8:59

You could avoid the correlated subquery with a filtering join, like:

select  *
from    leads l
join    users u
on      l.client_id = u.id
join    (
        select  lender_id
        ,       client_id
        ,       max(price) as max_price
        from    leads
        where   disabled = 0
        group by
        ,       client_id
        ) tl
on      tl.lender_id = l.lender_id
        and tl.client_id = l.client_id
        and tl.max_price = l.price
WHERE   l.lender_id = 1
        and l.claimed_date is null
        and l.disabled = 0
        and u.last_activity_date > '2012-01-13 02:42:10'
group by

If there is more than one lead with the highest price, this version will return all of them.

| improve this answer | |

If I understand correctly, you are trying to have a "greatest-n-per-group" query. Check this version:

FROM users AS u 
  INNER JOIN leads AS l 
    ON l.id = 
       ( SELECT id
         FROM leads
         WHERE client_id = u.id
           AND lender_id = 1
           AND claimed_date IS NULL
           AND disabled = 0
         ORDER BY price DESC
         LIMIT 0, 1
WHERE (u.last_activity_date > '2012-01-13 02:42:10')
ORDER BY u.last_activity_date DESC
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.