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 ?
Advertisement
Answer
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
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