Get the first value of a column based on a date column partitioned by Id

Question

Goal: I would like my output to show each distinct subscription_id, the first transaction_id based on the earliest date.

Problem: subscription_ids in my output are not distinct.

Situation: I have several columns in a table but only three are useful:

• subscription_id
• transaction_id
• issue_date

Each subscription_id can have multiple transaction_id. I need to pick the first transation_id for each subscription_id based on the earliest date.

Query: I tried the following two queries thinking they should give me the same results but both are different. In addition, both return duplicate subcription_ids when only DISTINCT subscription_id should come out.

SELECT DISTINCT t.subscription_id
    ,t.transation_id    
    ,MIN(t.issue_date)  As Min_Id
FROM table AS t
WHERE issue_date >= '2019-01-21'
GROUP BY t.subscription_id, t.transation_id
ORDER BY subscription_id desc


SELECT DISTINCT t.subscription_id
    ,FIRST_VALUE(t.transation_id)
        OVER (PARTITION BY t.subscription_id ORDER BY t.issue_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Min_id
    ,issue_date
FROM table AS t
WHERE issue_date >= '2019-01-21'
ORDER BY subscription_id desc

I ran mock up data as such and it worked.

INSERT INTO #test1
VALUES  
('2018-11-01', 'ABC@gmail.com')
,('2018-11-02', 'ABC@gmail.com')
,('2018-11-03', 'ABC@gmail.com')
,('2018-07-01', 'DEF@gmail.com')
,('2018-07-02', 'DEF@gmail.com')


SELECT DISTINCT EMAIL
    ,FIRST_VALUE(login_time)
        OVER(PARTITION BY email ORDER BY LOGIN_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATES
FROM #test1

Mock-up Output:
    EMAIL           DATES
    ABC@gmail.com  2018-11-01
    DEF@gmail.com  2018-07-01

What am i doing wrong or whats different?

Answer 1

you can try like below by using subquery

  select * from 
    (SELECT *,
            row_number() OVER(PARTITION BY email ORDER BY LOGIN_TIME) AS rn
    FROM #test1
    ) t where t.rn=1