How to join two tables based on FIRST VALUE of a group

Question

Objective: I would like to join two tables based on the first value of Id column grouped by subscription Id column ordered by created_at column.

Situation:

Table1 looks like this:

id  channel trx_date
123 organic 01/01/2019 05:00:00
234 direct  01/01/2019 05:01:00
987 add     01/01/2019 10:00:00
654 organic 01/01/2019 10:15:00

Table2:

subscription_id id  os      created_at
sub890         123  mac     01/01/2019 05:00:01
sub890         234  mac     01/01/2019 05:01:01
sub111         987  windows 01/01/2019 10:00:01
sub111         654  mac     01/01/2019 10:20:01

I need to take the earliest Id in table 2 grouped by subscription Id and inner join it with Table 1. So in this example, my output would be

subscription_id id  os      created_at id       channel trx_date
sub890          123 mac     01/01/2019 05:00:01 organic 01/01/2019 05:00:00
sub111          987 windows 01/01/2019 10:00:01 add     01/01/2019 10:00:00

What i tried: I thought about using the FIRST_VALUE but im stuck as to how I can connect them

SELECT t1.*, 
  t2.subscription_id,
  t2.os,
  t2.created_at, 
  FIRST_VALUE(t2.id) OVER (PARTITION BY t2.subscription_id ORDER BY t2.created_at ASC) as Min_Id
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.Min_id

Fiddle info:

CREATE TABLE table1
    ([id] varchar(13), [channel] varchar(50), [trx_date] Datetime)


INSERT INTO table1
VALUES
    ('123', 'organic', '2019-01-01 05:00:00'),
    ('234', 'direct', '2019-01-01 05:01:00'),
    ('987', 'add', '2019-01-01 10:00:00'),
    ('654', 'organic', '2019-01-01 10:15:00')

CREATE TABLE table2
    ([subscription_id] varchar(13),[id] varchar(13), [os] varchar(10), [created_at] Datetime)

INSERT INTO table2
VALUES
    ('sub890', '123', 'mac', '2019-01-01 05:00:01'),
    ('sub890', '234', 'mac', '2019-01-01 05:01:01'),
    ('sub111', '987', 'windows', '2019-01-01 10:00:01'),
    ('sub111', '654', 'mac', '2019-01-01 10:20:01')

Obviously this doesn't work due to the ON clause. Does this situation require a row_number function with a cross apply instead? Is there a better way of doing this? Is FIRST_VALUE the wrong function to use ?

Answer 1

you can use row_number() with order by create_at date which will take 1st id

with cte as
(
select *,row_number() over(partition by subscription_id  order by created_at) rn
  from tabl2
) select cte.*,t1.* from cte 
       join table1 t1 on cte.id =t1.id
  where cte.rn=1

demo link

subscription_id id  os    created_at           rn   id  channel  trx_date
sub890          123 mac   01/01/2019 05:00:01   1   123 organic 01/01/2019 05:00:00
sub111          987 windows 01/01/2019 10:00:01 1   987 add     01/01/2019 10:00:00