Order by showing oldest result from join when grouped

Question

Heres my query:

select l.id, l.name, l.postcode, l.the_date, d.id as dealer_id, d.name as dealer_name,
(select count(`id`) from `lead_copies` where `id_lead`=l.id) as total_copies,
(select count(`id`) from `assigns` where `id_lead`=l.id) as total_assigns
from `leads` as l
left join `assigns` as a on a.id_lead = l.id
left join `dealers` as d on d.id = a.id_dealer
group by a.id_lead
order by l.the_date desc

The assigns table also has an int field which contains unix timestamps called the_date.

The problem is, the dealer_name is coming up for the oldest row in the assigns table. I want the dealer_name of the newest row with the id_lead of l.id in the assigns table.

How do I do this? I can't figure it out. If I change the order by to a.the_date I get unwanted results in that I want these ordered by the lead date, not the assign date. I only want the dealer names ordered by the assign date, if that makes sense.

Heres a better idea of what I need but obviously this query doesn't work either:

select l.id, l.name, l.postcode, l.the_date, d.id as dealer_id, d.name as dealer_name,
(select count(`id`) from `lead_copies` where `id_lead`=l.id) as total_copies,
(select count(`id`) from `assigns` where `id_lead`=l.id) as total_assigns,
(select `id_dealer` from `assigns` where `id_lead`=l.id order by `id` desc limit 1) as last_dealer
from `leads` as l
left join `dealers` as d on d.id = last_dealer
order by l.the_date desc

FINAL EDIT: All I want to do is merge the following into 1 single SQL query:

$sql = mysql_query("select l.id, l.name, l.postcode, l.the_date,
                    (select count(`id`) from `lead_copies` where `id_lead`=l.id) as total_copies,
                    (select count(`id`) from `assigns` where `id_lead`=l.id) as total_assigns
                    from `leads` as l
                    order by l.the_date desc");                     

while ($row = mysql_fetch_assoc($sql))
{
    $lead = array();

    foreach ($row as $k => $v)
        $lead[$k] = htmlspecialchars(stripslashes($v), ENT_QUOTES);

    $sql2 = mysql_query("select d.id as dealer_id, d.name as dealer_name
                        from `assigns` as a
                        left join `dealers` as d on d.id = a.id_dealer
                        where a.id_lead = ".$lead['id']."
                        order by a.the_date desc
                        limit 1");

    while ($row2 = mysql_fetch_assoc($sql2))
    {
        foreach ($row2 as $k2 => $v2)
            $lead[$k2] = htmlspecialchars(stripslashes($v2), ENT_QUOTES);               
    }

    echo '<pre>';
    print_r($lead);
    echo '</pre>';
}

Is this possible? I am literally too dumb to figure this out.

Your `group by` is invalid (standard) SQL. Every other DBMS will reject that query, but MySQL will let you get away with that by choosing random (they call it "indeterminate" though) rows from each group: http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/ — , Dec 01 '14 at 13:39
I read your article. The problem is I don't know how to do what I need to do properly. Lack of knowledge I guess. I've tried googling it but can't seem to find a solution. — inkd, Dec 01 '14 at 13:43
Tables are not ordered. Answer sets (which aren't sets), which is what queries return, are ordered. Ordering a subquery isn't even legal in the SQL standard. — philipxy, Dec 01 '14 at 13:57
As @a_horse_with_no_name has stated, you have to fix your group by statement (everything - except aggregate functions like min, max etc - you selected in the SELECT clause must also appear in the GROUP BY clause). But, why do you need this group by anyway? — iris, Dec 01 '14 at 14:00
What does "the newest row with the id_lead of l.id" mean? If you wnat the one with the newest associated date, write a subquery to get that date via group by & max or by self-join and >= then use that value to restrict the result. See [Nested VIEW ignores ORDER BY](https://stackoverflow.com/questions/26787276/nested-view-ignores-order-by). — philipxy, Dec 01 '14 at 14:09
@DionSoft Try searching SO for an answer - I'll give you another tag as a clue... — Strawberry, Dec 01 '14 at 14:32
I still just don't get it. Maybe its the lack of sleep but all I want to do is grab the dealer name and id for the most recent assign and spit it out along with the lead info. — inkd, Dec 01 '14 at 15:26
Post-edit: What, assigns have dates? Nothing in your first two queries asks for a max assign date! — philipxy, Dec 01 '14 at 15:53
yeah they do.....i tried adding max(a.the_date) to the original query and the results are the same... — inkd, Dec 01 '14 at 16:04

score 1 · Accepted Answer · answered Dec 01 '14 at 16:16

Just taking your subquery for last_dealer and moving into your join and geting max the_date instead of id:

select l.id, l.name, l.postcode, l.the_date, d.id as dealer_id, d.name as dealer_name,
    (select count(`id`) from `lead_copies` where `id_lead`=l.id) as total_copies,
    (select count(`id`) from `assigns` where `id_lead`=l.id) as total_assigns,
    d.id as last_dealer
from `leads` as l
left join `dealers` as d
on d.id =
    (select `id_dealer` 
    from `assigns`
    where `id_lead`=l.id
    order by `the_date` desc
    limit 1)
order by l.the_date desc

(But don't use order by + limit, use max.)

Order by showing oldest result from join when grouped

1 Answers1