4

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)
2
  • 1
    Define "first" match. Jan 19, 2017 at 17:23
  • I do have a column for customer sign up date in the person table that can be used for the first match.
    – Michael
    Jan 19, 2017 at 17:40

1 Answer 1

2

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;
3
  • I'm glad to be able to help.
    – McNets
    Jan 19, 2017 at 18:21
  • 1
    @Michael I wonder how this solved the issue, considering that MySQL does not have window function likeROW_NUMBER(). Jan 19, 2017 at 18:37
  • I should think about it, always the same misreading error.
    – McNets
    Jan 19, 2017 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.