Joining two tables and finding the earliest date SQL

Question

I have the following two tables and I need to get the following result:

Table 1

(A, 1, 01/01/2015),
(A, 1, 10/01/2015),
(A, 2, 20/01/2015),
(A, 2, 01/05/2015),
(B, 1, 20/02/2014),
(B, 1, 20/02/2015),
(B, 2, 20/02/2016),
(B, 2, 06/05/2015)

Table 2

(A, 1, 123),
(A, 1, 123),
(A, 2, 234),
(A, 2, 234),
(B, 1, 123),
(B, 2, 123),

I want to return the earliest date of each distinct combo:

(A, 123, 01/01/2015),
(A, 234, 20/01/2015),
(B, 123, 20/02/2014)

Code I have tried:

DECLARE @table1 TABLE (letter1 CHAR(1), num1 INT, date1 INT)  
DECLARE @table2 TABLE (letter1 CHAR(1), num1 INT, num2 INT)  

INSERT INTO @table1 VALUES    
    ('A', 1, 01012015),  
    ('A', 1, 10012015),  
    ('A', 2, 20012015),  
    ('A', 2, 01052015),  
    ('B', 1, 20022014),  
    ('B', 1, 20022015),  
    ('B', 2, 20022016),  
    ('B', 2, 06052015)  

INSERT INTO @table2 VALUES   
    ('A', 1, 123),  
    ('A', 1, 123),   
    ('A', 2, 234),  
    ('A', 2, 234),  
    ('B', 1, 123),  
    ('B', 2, 123)  



SELECT DISTINCT [@table1].letter1,  num2, MIN(date1) FROM @table1
INNER JOIN @table2 ON [@table1].letter1 = [@table2].letter1 AND [@table1].num1 = [@table2].num1
GROUP BY [@table1].letter1, [@table1].num1, num2

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Khalil, Jul 26 '18 at 16:03

score 1 · Answer 1 · answered Jul 26 '18 at 16:03

You can use row_number() function :

select top (1) with ties t.letter1, t2.num2, t.date1
from table1 t inner join
     table2 t2
     on t2.letter1 = t.letter1 AND t2.num1 = t.num1
order by row_number() over (partition by t2.letter1, t2.num2 order by t.date1 desc);

score 0 · Answer 2 · answered Jul 26 '18 at 15:54

Just giving a try . may be add date1 in group by cluase SELECT DISTINCT [@table1].letter1, num2, MIN(date1) FROM @table1 INNER JOIN @table2 ON [@table1].letter1 = [@table2].letter1 AND [@table1].num1 = [@table2].num1 GROUP BY [@table1].letter1, [@table1].num1, num2,date1

score 0 · Answer 3 · answered Aug 16 '18 at 08:50

;with cte as 
(
  select  name, IIF(c = 1, 0, id) id, value from    --- Here We separet the whole into two groups. 
 (
   select name, id, value, count(*) c from #table2 group by name, id, value 
 ) ct                                               ---- Here one group (B) has same value (123).
                                                    ---- And another group (A) have diff values (123,234))
)select c.name, c.value, min(t1.yyymmdd) from cte c
join #table1 t1 
on c.name = t1.name 
and c.id = t1.id                                    ------ Id's join must. Because it has two different ids 
and c.id <> 0                                       ------ 'A' group has been joined
group by c.name, value union 
select c.name, c.value, min(t1.yyymmdd) Earlier_date from cte c 
join #table1 t1 
on c.name = t1.name                                 ------ Id's join is no need. Because it has one   id.
and c.id = 0                                        ------ 'B' group has been joined
group by c.name, value

Joining two tables and finding the earliest date SQL

3 Answers3