CODEWITHSUNDEEP
sqlleft-joininner-jointableau-apimaxdate
Rosadocc
Rosadocc

Reputation: 25

SQL Left Outer Join but only pull max date

I noticed variations of this question has been asked but none seem to work for me (or I wasn't looking right). I'm still learning SQL so please bear with me.

I have two tables I'm looking to join. I want all records from Table C and only ones that match from Table P. So I assume a left outer join?

I want all columns from both tables and they will be joined by "PartNo" in Table C and "ItemNo" in Table P.

The tricky part for me is that along with this join, I am looking to only pull the latest "ItemNo" based on date (PDate) in Table P.

Thanks in advance!

Upvotes: 1

Views: 425

Answers (1)

KindaTechy
KindaTechy

Reputation: 1173

This can be done with an outer apply

select *
    from TableC c
    outer apply (
        select top 1 *
            from TableP
                where itemno = c.partno
                order by pdate desc
    ) p

Upvotes: 2

Related Questions