Link to home
Start Free TrialLog in
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
Avatar of Louis01
Louis01
Flag of South Africa image

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

ASKER CERTIFIED SOLUTION
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Select
A.accountname
,MAX(B.actiondate) ActionDate
from B
inner join A on A.account=B.account
GROUP BY A.accountname
ORDER BY A.accountname
Avatar of Steynsk

ASKER

Thank you Hua Min Chen it works excelently