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

Posted on 2013-09-04
Medium Priority
Last Modified: 2013-09-04
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
Question by:Steynsk
LVL 11

Expert Comment

ID: 39462914
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

LVL 11

Accepted Solution

HuaMinChen earned 2000 total points
ID: 39462917
with cte as
(Select B.actiondate, A.accountname,ROW_NUMBER() OVER(PARTITION BY b.actiondate ORDER BY A.accountname desc) rn from B inner join A on A.account=B.account)
select actiondate,accountname
from cte
where rn=1;

Open in new window

LVL 17

Expert Comment

by:Barry Cunney
ID: 39462919
,MAX(B.actiondate) ActionDate
from B
inner join A on A.account=B.account
GROUP BY A.accountname
ORDER BY A.accountname

Author Closing Comment

ID: 39463405
Thank you Hua Min Chen it works excelently

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question