SQLTeam.com | Weblogs | Forums

Distinct records from multiple databases

I would like to return distinct user count per organization by earliest created date. Example:
username - userorg - createddate
user1 - org1 - 2018-04-13
user2 - org1 - 2019-05-23
user3 - org2 - 2020-04-20
user1 -org4 - 2020-09-18
user4 -org5 - 2020-07-19
user2 -org6 -2020-10-31
user5 -org2 - 2019-02-20
user2 -org1 - 2017-06-09

Result
user1 - org1 - 2018-04-13
user2 -org1 - 2017-06-09
user3 - org2 - 2020-04-20
user4 -org5 - 2020-07-19
user5 -org2 - 2019-02-20

Result Count
2 org1
2 org2
1 org5

;WITH first_org AS(SELECT username, userorg
, row_num=ROW_NUMBER() OVER (PARTITION BY username ORDER BY createddate,userorg)
FROM myTable)
SELECT count(distinct username), userorg 
FROM first_org 
WHERE row_num=1
GROUP BY userorg

The OVER...ORDER BY includes userorg in case there are users in 2 orgs on the same date, it will choose the alphabetically first org ID/name. Without that you might get inconsistent results if you have users in 2 orgs on the same date.