SQL Left Join on First Match Only

I've got two tables of data, one at a person level and one at a district level. I'd like to get a query that adds the district information to only the first person it finds in the district and leaves the rest null. The person table looks like:

IDNo    FirstName   LastName    DistrictID    SignUpDate
----------------------------------------------------------
123     bob         smith       1             1/19/2017
234     john        willis      1             1/18/2017
456     john        doe         2             1/17/2017
678     jane        doe         2             1/16/2017
890     jeff        bridges     3             1/19/2017
135     bill        doe         3             1/19/2017
246     sally       abby        4             1/14/2017
357     michael     jordan      4             1/12/2017

And the district table is:

DistrictID    DistrictName   BigDistrict    
-------------------------------------------
1             district1      big1
2             district2      big1
3             district3      big2
4             district4      big2

What I'd like to end up with:

IDNo    FirstName   LastName    DistrictID     DistrictName     BigDistrict
-----------------------------------------------------------------------------
123     bob         smith       1              district1        big1
234     john        willis      1              (NULL)           (NULL)
456     john        doe         2              district2        big1              
678     jane        doe         2              (NULL)           (NULL)
890     jeff        bridges     3              district3        big2
135     bill        doe         3              (NULL)           (NULL)
246     sally       abby        4              district4        big2
357     michael     jordan      4              (NULL)           (NULL)

I think this should solve the question.

I've numbered Person table, partitioning by DistrictID and ordered by SingUpDate and IDNo.

IDNo    FirstName   LastName    DistrictID    SignUpDate   Row Number
---------------------------------------------------------------------
123     bob         smith       1             1/19/2017         2
234     john        willis      1             1/18/2017         1
456     john        doe         2             1/17/2017         2
678     jane        doe         2             1/16/2017         1
890     jeff        bridges     3             1/19/2017         1 
135     bill        doe         3             1/19/2017         2
246     sally       abby        4             1/14/2017         2
357     michael     jordan      4             1/12/2017         1

Then adds District information to all rows where row number is equal 1.

This should works for MySql:

select pn.IDNo, pn.FirstName, pn.LastName, pn.DistrictID, pn.SignUpDate, 
       case when pn.rn=1 then d.DistrictName else '' end as DistrictName,
       case when pn.rn=1 then d.BigDistrict else '' end as BigDistrict, 
from 
    (select 
           @rn := case when @district = DistrictID then @rn + 1 else @rn := 1 end as rn,
           @district := DistrictID as DistrictID,
           p.IDNo, p.FirstName, p.LastName, p.SignUpDate
    from
        (select @rn := 1) i,
        (select @district := DistrictID as DistrictID, IDNo, FirstName, LastName, SignUpDate 
         from Person order by SignUpDate , IDNo) p
    ) pn
    inner join District d on d.DistrictID = pn.DistrictID

And this for other DBMS that allows CTE:

WITH p AS
(
    SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
           ROW_NUMBER() OVER (PARTITION BY DisctictID ORDER BY SignUpDate, IDNo) RN
    FROM Person
)
SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
       CASE WHEN RN = 1 THEN d.DistrictName ELSE '' END AS DistrictName,
       CASE WHEN RN = 1 THEN d.BigDistrict ELSE '' END AS BigDistrict,
FROM p
     JOIN District d ON p.DistrictID = d.DistrictID
ORDER BY p.IDNo;

StackExchange Database Administrators Q#161626, answer score: 2