Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For the following query:

SELECT l.*
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?

share|improve this question
    
which mysql version are you using? can you post the explain for that query? –  golimar Apr 12 '12 at 8:59

2 Answers 2

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
                lender_id
        ,       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
        l.client_id

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

share|improve this answer

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

SELECT l.*
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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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