Avatar of Steynsk
SteynskFlag for Netherlands asked on

All fields from MS SQL table A and only the top from tableB

Hi Experts,

My MS SQL tables A and B have one to many relation. One entry in table A can have multiple related entries in table B.

With the query below I get a list of records so that works.

Select A.accountname, B.actiondate from B inner join A on A.account=B.account

Open in new window

The output is like:

accountname      actieondate
JohnD      2013-2-12
JohnD      2013-2-16
JohnD      2013-3-24
MaryC      2013-5-24
GeorgeB      2013-3-11
GeorgeB      2013-6-21

But I would like to get only the latest action dates only like in the output example below

accountname      actieondate
JohnD      2013-3-24
MaryC      2013-5-24
GeorgeB      2013-6-21
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

select A.accountname
     , max(B.actiondate) as actiondate
  from B 
        inner join A 
    on A.account=B.account
 group by A.accountname

Open in new window

Peter Chan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Barry Cunney

,MAX(B.actiondate) ActionDate
from B
inner join A on A.account=B.account
GROUP BY A.accountname
ORDER BY A.accountname

Thank you Hua Min Chen it works excelently
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy