Join tables to find distinct rows if any duplicate columns on join are fetched on the basis of date-CodePudding

I have two tables I need to join these table and there is a possibility that joined table might return duplicate rows but there is column updated date which will be unique so I need to fetch record from these tables and get distinct records from second table

Table-1

Id	AccountKey
1	12
2	13

Table-2

Rolekey	Account Key	Date
1	12	2-12-2022
2	12	1-12-2022
3	13	1-12-2022

In the above table I except the result as below Expections:-

Id	AccountKey	Date
1	12	2-12-2022
2	13	1-12-2022

But I am getting all the rows means 3, below is what I tried

select table1.id,table1.accountkey,table2.date 
from table1 table1 
JOIN table2 table2 
ON table1.accountkey=table2.accountkey

CodePudding user response：

Let's say we're grouping by id and accountKey Query below will get as your desired result.

SELECT a.id, a.accountKey, MAX(b.cdate)
FROM table1 a
JOIN table2 b ON a.accountKey = b.accountKey
GROUP BY a.id, a.accountKey

CodePudding user response：

Try this

drop table if exists one;
drop table if exists two;

create table one
(
  ID         [Int]
, AccountKey [Int]
)
;

insert into one
values (1, 12)
     , (2, 13)
;

create table two
(
  Rolekey    [Int]
, AccountKey [Int]
, Date       [Date]
)
;

insert into two
values (1, 12, '2022-12-02')
     , (2, 12, '2022-12-01')
     , (3, 13, '2022-12-01')
;

select one.id
     , one.AccountKey
     , max(Date) as Date
from one, two
where one.AccountKey = two.AccountKey
group by one.id
       , one.AccountKey
;