Take the time when customer first time register in any system

Question

I have some tables in DB:

C_Master_USER - that cointains relations between users (ID_USER) and their MasterID (ID_Master) - users that are from difrrent systems but they use f.e. the same email will have the same MasterID

V_DL_C_USER - that contains ID_Source and ID_SYS_TYPE for each user

C_Source - that contains enitity names for source system

C_SYS_TYPE - that contains entity names for system types

and my simple task is to get each MasterID, their RegistratonDate and the Source of first registration in any system

SELECT top 50 
MasterID, RegistrationDate, SourceName, SystemName
FROM (
SELECT
    a.ID_MASTER as MasterID, 
    a.DATE_N as RegistrationDate,
    c.Name2 as SourceName,
    d.Name_2 as SystemName,
    ROW_NUMBER() OVER(ORDER BY a.DATE_N ASC) as RN
FROM C_MASTER_USE a 
join V_DL_C_USER b on a.ID_USER = b.ID 
join c_source c on b.ID_Source = c.ID
join C_SYS_TYPE d on b.ID_SYS_TYPE = d.ID
) t
where RN = 1

However, the query results only 1, the latest user in all systems.

MasterID    RegistrationDate        SourceName       SystemName

8825938     2020-11-10 08:17:30     Mobile App       Club

Answer 1

If you want one row for each MasterId use PARTITION BY:

SELECT top 50 
MasterID, RegistrationDate, SourceName, SystemName
FROM (
SELECT
    a.ID_MASTER as MasterID, 
    a.DATE_N as RegistrationDate,
    c.Name2 as SourceName,
    d.Name_2 as SystemName,
    ROW_NUMBER() OVER(PARTITION BY a.ID_MASTER ORDER BY a.DATE_N ASC) as RN
FROM C_MASTER_USE a 
join V_DL_C_USER b on a.ID_USER = b.ID 
join c_source c on b.ID_Source = c.ID
join C_SYS_TYPE d on b.ID_SYS_TYPE = d.ID
) t
where RN = 1