CODEWITHSUNDEEP
mysqlsql
Strong Like Bull
Strong Like Bull

Reputation: 11297

How to retrieve Max Date from a left outer join?

I have 2 tables client_headquarter & clients

client_headquarter: id

clients: id, headquarterid, clentname, tscreated

I want to show all the headquarters and along with each headquarter show the "most recent" client (ts_created) if it exists else a blank in its place. I want it all to be sorted by headquarters that have no clients, older clients, and latest clients at the bottom.

Can someone assist with this query?

Upvotes: 2

Views: 6569

Answers (3)

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59453

select 
    ch.id as [Headquarter ID],
    c.clientname as [Most Recent Client Name],
    c.tscreated as [Date Created]
from 
    client_headquarter ch
    left join 
    (select 
        headquarterid,
        max(tscreated)
    from
        clients
    group by
        headquarterid
    ) recent on recent.headquarterid = ch.id
    left join clients c on c.headquarterid = ch.headquarterid and c.tscreated = recent.tscreated
order by
    c.tscreated

This will create multiple rows for a single headquarter if multiple clients have the same created date. If this is not desirable, then some explicit means of distinguishing a winner will have to be defined and implemented.

Upvotes: 0

mtone
mtone

Reputation: 1787

Something like..

> select chq.id, count(clientname), max(tscreated) from clients c
> left outer join client_headquarter chq on c.hearquarterid = chq.id
> group by chq.id
> order by count(clientname) DESC, max(tscreated) DESC

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65537

SELECT client_headquarter.id, max(clients.tscreated)
FROM client_headquarter 
LEFT OUTER JOIN clients ON clients.headquarterid = client_headquarter.id
GROUP BY client_headquarter.id
ORDER BY MAX(clients.tscreated) ASC

Upvotes: 3

Related Questions