Display one row over another if there are two

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
2
down vote

favorite












Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.



If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.



Any way to do that?



SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID


Example result set: 



1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2


Would like this back: 



1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2





sql-server sql-server-2017







share|improve this question









New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • I don't think you need that subquery in your where condition
    – Mattia Nocerino
    3 hours ago






  • 1




    Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
    – ypercubeᵀᴹ
    2 hours ago










  • Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
    – RDFozz
    2 hours ago










  • @MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
    – Isaac Holmes
    2 hours ago






  • 1




    So Address (ID) is not the primary key of Address. OK.
    – ypercubeᵀᴹ
    1 hour ago
















up vote
2
down vote

favorite












Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.



If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.



Any way to do that?



SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID


Example result set: 



1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2


Would like this back: 



1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2





sql-server sql-server-2017







share|improve this question









New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • I don't think you need that subquery in your where condition
    – Mattia Nocerino
    3 hours ago






  • 1




    Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
    – ypercubeᵀᴹ
    2 hours ago










  • Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
    – RDFozz
    2 hours ago










  • @MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
    – Isaac Holmes
    2 hours ago






  • 1




    So Address (ID) is not the primary key of Address. OK.
    – ypercubeᵀᴹ
    1 hour ago












up vote
2
down vote

favorite









up vote
2
down vote

favorite











Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.



If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.



Any way to do that?



SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID


Example result set: 



1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2


Would like this back: 



1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2





sql-server sql-server-2017







share|improve this question









New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.



If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.



Any way to do that?



SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID
from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
 SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID


Example result set: 



1234 John Williams Sacramento CA 1 
1234 John Williams Portland OR 2 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2


Would like this back: 



1234 John Williams Sacramento CA 1 
1111 Mary Johnson Sacramento CA 1 
1112 James Stoller Carson City NV 2





sql-server sql-server-2017




sql-server sql-server-2017






share|improve this question









New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 2 hours ago









MDCCL

6,30731740




6,30731740






New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 3 hours ago









Isaac Holmes

132




132




New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











  • I don't think you need that subquery in your where condition
    – Mattia Nocerino
    3 hours ago






  • 1




    Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
    – ypercubeᵀᴹ
    2 hours ago










  • Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
    – RDFozz
    2 hours ago










  • @MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
    – Isaac Holmes
    2 hours ago






  • 1




    So Address (ID) is not the primary key of Address. OK.
    – ypercubeᵀᴹ
    1 hour ago
















  • I don't think you need that subquery in your where condition
    – Mattia Nocerino
    3 hours ago






  • 1




    Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
    – ypercubeᵀᴹ
    2 hours ago










  • Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
    – RDFozz
    2 hours ago










  • @MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
    – Isaac Holmes
    2 hours ago






  • 1




    So Address (ID) is not the primary key of Address. OK.
    – ypercubeᵀᴹ
    1 hour ago















I don't think you need that subquery in your where condition
– Mattia Nocerino
3 hours ago




I don't think you need that subquery in your where condition
– Mattia Nocerino
3 hours ago




1




1




Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
– ypercubeᵀᴹ
2 hours ago




Are you sure about the Left Join Address on Employees.ID = Address.ID? Should it not be Left Join Address on Employees.ID = Address.EmployeeID?
– ypercubeᵀᴹ
2 hours ago












Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
– RDFozz
2 hours ago




Also, checking the value of Site.ID in the WHERE clause, without an allowance for Site.ID to be NULL, effectively makes your joins act like INNER JOINs. If you really want rows from Employees without matches in Address or Site, you need to add OR Site.Id IS NULL to the WHERE clause.
– RDFozz
2 hours ago












@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
– Isaac Holmes
2 hours ago




@MattiaNocerino - yes you are correct, I no longer need the subquery, left over from another iteration of testing. Adding it as part of the left join for Site works as well.
– Isaac Holmes
2 hours ago




1




1




So Address (ID) is not the primary key of Address. OK.
– ypercubeᵀᴹ
1 hour ago




So Address (ID) is not the primary key of Address. OK.
– ypercubeᵀᴹ
1 hour ago










3 Answers
3






active

oldest

votes

















up vote
2
down vote



accepted










Wrap your query in a common table expression and add a row number column like this



SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn


Then, select from the common table expression where rn = 1






share|improve this answer




















  • After a little more research and leveling up the SQL query skills this worked great. Thanks.
    – Isaac Holmes
    28 mins ago










  • @IsaacHolmes - Awesome, glad it worked for you!
    – Scott Hodgin
    27 mins ago

















up vote
1
down vote













This can be achieved by joining the table Site twice:



SELECT e.ID
 , e.FName
 , e.LName
 , e.City
 , a.State
 , s.ID
 FROM Employees AS e
 JOIN Address AS a ON a.ID = e.ID
 JOIN Site AS s ON s.ID = a.ADID
 LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
 AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
 AND z.ID IS NULL 
 ORDER BY e.ID ASC
;





share|improve this answer






















  • Very nice working in-all-database solution.
    – Luciano Andress Martini
    3 hours ago







  • 1




    Duplicate commas detected ;)
    – ypercubeᵀᴹ
    2 hours ago










  • On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
    – ypercubeᵀᴹ
    2 hours ago







  • 1




    @ypercubeᵀᴹ Yep you are absolutely right!
    – Kondybas
    2 hours ago

















up vote
0
down vote













SELECT * FROM (
 SELECT DISTINCT
 Employees.ID, 
 Employees.FName,
 Employees.LName,
 Address.City,
 Address.State,
 Site.ID,
 Row_Number() over(partition by Employees.ID order by Site.ID) as RN
 from Employees
 Left Join Address on Employees.ID = Address.ID
 Left Join Site on Address.ADID = Site.ID 
 and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1
Order by Employees.ID, Site.ID





share|improve this answer








New contributor




Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );






    Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.









     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218628%2fdisplay-one-row-over-another-if-there-are-two%23new-answer', 'question_page');

    );

    Post as a guest

















    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    2
    down vote



    accepted










    Wrap your query in a common table expression and add a row number column like this



    SELECT DISTINCT
     Employees.ID, 
     Employees.FName,
     Employees.LName,
     Address.City,
     Address.State,
     Site.ID,
    Row_Number() over(partition by Employees.ID order by Site.id) as rn


    Then, select from the common table expression where rn = 1






    share|improve this answer




















    • After a little more research and leveling up the SQL query skills this worked great. Thanks.
      – Isaac Holmes
      28 mins ago










    • @IsaacHolmes - Awesome, glad it worked for you!
      – Scott Hodgin
      27 mins ago














    up vote
    2
    down vote



    accepted










    Wrap your query in a common table expression and add a row number column like this



    SELECT DISTINCT
     Employees.ID, 
     Employees.FName,
     Employees.LName,
     Address.City,
     Address.State,
     Site.ID,
    Row_Number() over(partition by Employees.ID order by Site.id) as rn


    Then, select from the common table expression where rn = 1






    share|improve this answer




















    • After a little more research and leveling up the SQL query skills this worked great. Thanks.
      – Isaac Holmes
      28 mins ago










    • @IsaacHolmes - Awesome, glad it worked for you!
      – Scott Hodgin
      27 mins ago












    up vote
    2
    down vote



    accepted







    up vote
    2
    down vote



    accepted






    Wrap your query in a common table expression and add a row number column like this



    SELECT DISTINCT
     Employees.ID, 
     Employees.FName,
     Employees.LName,
     Address.City,
     Address.State,
     Site.ID,
    Row_Number() over(partition by Employees.ID order by Site.id) as rn


    Then, select from the common table expression where rn = 1






    share|improve this answer












    Wrap your query in a common table expression and add a row number column like this



    SELECT DISTINCT
     Employees.ID, 
     Employees.FName,
     Employees.LName,
     Address.City,
     Address.State,
     Site.ID,
    Row_Number() over(partition by Employees.ID order by Site.id) as rn


    Then, select from the common table expression where rn = 1







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered 2 hours ago









    Scott Hodgin

    15.6k11535




    15.6k11535











    • After a little more research and leveling up the SQL query skills this worked great. Thanks.
      – Isaac Holmes
      28 mins ago










    • @IsaacHolmes - Awesome, glad it worked for you!
      – Scott Hodgin
      27 mins ago
















    • After a little more research and leveling up the SQL query skills this worked great. Thanks.
      – Isaac Holmes
      28 mins ago










    • @IsaacHolmes - Awesome, glad it worked for you!
      – Scott Hodgin
      27 mins ago















    After a little more research and leveling up the SQL query skills this worked great. Thanks.
    – Isaac Holmes
    28 mins ago




    After a little more research and leveling up the SQL query skills this worked great. Thanks.
    – Isaac Holmes
    28 mins ago












    @IsaacHolmes - Awesome, glad it worked for you!
    – Scott Hodgin
    27 mins ago




    @IsaacHolmes - Awesome, glad it worked for you!
    – Scott Hodgin
    27 mins ago












    up vote
    1
    down vote













    This can be achieved by joining the table Site twice:



    SELECT e.ID
     , e.FName
     , e.LName
     , e.City
     , a.State
     , s.ID
     FROM Employees AS e
     JOIN Address AS a ON a.ID = e.ID
     JOIN Site AS s ON s.ID = a.ADID
     LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
     AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
     WHERE s.Type = 'Primary'
     AND z.ID IS NULL 
     ORDER BY e.ID ASC
    ;





    share|improve this answer






















    • Very nice working in-all-database solution.
      – Luciano Andress Martini
      3 hours ago







    • 1




      Duplicate commas detected ;)
      – ypercubeᵀᴹ
      2 hours ago










    • On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
      – ypercubeᵀᴹ
      2 hours ago







    • 1




      @ypercubeᵀᴹ Yep you are absolutely right!
      – Kondybas
      2 hours ago














    up vote
    1
    down vote













    This can be achieved by joining the table Site twice:



    SELECT e.ID
     , e.FName
     , e.LName
     , e.City
     , a.State
     , s.ID
     FROM Employees AS e
     JOIN Address AS a ON a.ID = e.ID
     JOIN Site AS s ON s.ID = a.ADID
     LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
     AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
     WHERE s.Type = 'Primary'
     AND z.ID IS NULL 
     ORDER BY e.ID ASC
    ;





    share|improve this answer






















    • Very nice working in-all-database solution.
      – Luciano Andress Martini
      3 hours ago







    • 1




      Duplicate commas detected ;)
      – ypercubeᵀᴹ
      2 hours ago










    • On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
      – ypercubeᵀᴹ
      2 hours ago







    • 1




      @ypercubeᵀᴹ Yep you are absolutely right!
      – Kondybas
      2 hours ago












    up vote
    1
    down vote










    up vote
    1
    down vote









    This can be achieved by joining the table Site twice:



    SELECT e.ID
     , e.FName
     , e.LName
     , e.City
     , a.State
     , s.ID
     FROM Employees AS e
     JOIN Address AS a ON a.ID = e.ID
     JOIN Site AS s ON s.ID = a.ADID
     LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
     AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
     WHERE s.Type = 'Primary'
     AND z.ID IS NULL 
     ORDER BY e.ID ASC
    ;





    share|improve this answer














    This can be achieved by joining the table Site twice:



    SELECT e.ID
     , e.FName
     , e.LName
     , e.City
     , a.State
     , s.ID
     FROM Employees AS e
     JOIN Address AS a ON a.ID = e.ID
     JOIN Site AS s ON s.ID = a.ADID
     LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
     AND z.ID < s.ID -- the smallest s.ID will get z.ID=NULL
     WHERE s.Type = 'Primary'
     AND z.ID IS NULL 
     ORDER BY e.ID ASC
    ;






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited 2 hours ago

























    answered 3 hours ago









    Kondybas

    2,11789




    2,11789











    • Very nice working in-all-database solution.
      – Luciano Andress Martini
      3 hours ago







    • 1




      Duplicate commas detected ;)
      – ypercubeᵀᴹ
      2 hours ago










    • On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
      – ypercubeᵀᴹ
      2 hours ago







    • 1




      @ypercubeᵀᴹ Yep you are absolutely right!
      – Kondybas
      2 hours ago
















    • Very nice working in-all-database solution.
      – Luciano Andress Martini
      3 hours ago







    • 1




      Duplicate commas detected ;)
      – ypercubeᵀᴹ
      2 hours ago










    • On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
      – ypercubeᵀᴹ
      2 hours ago







    • 1




      @ypercubeᵀᴹ Yep you are absolutely right!
      – Kondybas
      2 hours ago















    Very nice working in-all-database solution.
    – Luciano Andress Martini
    3 hours ago





    Very nice working in-all-database solution.
    – Luciano Andress Martini
    3 hours ago





    1




    1




    Duplicate commas detected ;)
    – ypercubeᵀᴹ
    2 hours ago




    Duplicate commas detected ;)
    – ypercubeᵀᴹ
    2 hours ago












    On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
    – ypercubeᵀᴹ
    2 hours ago





    On more serious matters, the AND z.Type = 'Primary' shouldn't be there. No row can have z.Type = 'Primary' AND z.ID IS NULL so this query as it is will return 0 rows.
    – ypercubeᵀᴹ
    2 hours ago





    1




    1




    @ypercubeᵀᴹ Yep you are absolutely right!
    – Kondybas
    2 hours ago




    @ypercubeᵀᴹ Yep you are absolutely right!
    – Kondybas
    2 hours ago










    up vote
    0
    down vote













    SELECT * FROM (
     SELECT DISTINCT
     Employees.ID, 
     Employees.FName,
     Employees.LName,
     Address.City,
     Address.State,
     Site.ID,
     Row_Number() over(partition by Employees.ID order by Site.ID) as RN
     from Employees
     Left Join Address on Employees.ID = Address.ID
     Left Join Site on Address.ADID = Site.ID 
     and Site.Type = 'Primary'
    ) as [AllEmpTable]
    Where [AllEmpTable].RN = 1
    Order by Employees.ID, Site.ID





    share|improve this answer








    New contributor




    Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.





















      up vote
      0
      down vote













      SELECT * FROM (
       SELECT DISTINCT
       Employees.ID, 
       Employees.FName,
       Employees.LName,
       Address.City,
       Address.State,
       Site.ID,
       Row_Number() over(partition by Employees.ID order by Site.ID) as RN
       from Employees
       Left Join Address on Employees.ID = Address.ID
       Left Join Site on Address.ADID = Site.ID 
       and Site.Type = 'Primary'
      ) as [AllEmpTable]
      Where [AllEmpTable].RN = 1
      Order by Employees.ID, Site.ID





      share|improve this answer








      New contributor




      Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.



















        up vote
        0
        down vote










        up vote
        0
        down vote









        SELECT * FROM (
         SELECT DISTINCT
         Employees.ID, 
         Employees.FName,
         Employees.LName,
         Address.City,
         Address.State,
         Site.ID,
         Row_Number() over(partition by Employees.ID order by Site.ID) as RN
         from Employees
         Left Join Address on Employees.ID = Address.ID
         Left Join Site on Address.ADID = Site.ID 
         and Site.Type = 'Primary'
        ) as [AllEmpTable]
        Where [AllEmpTable].RN = 1
        Order by Employees.ID, Site.ID





        share|improve this answer








        New contributor




        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct. 









        SELECT * FROM (
         SELECT DISTINCT
         Employees.ID, 
         Employees.FName,
         Employees.LName,
         Address.City,
         Address.State,
         Site.ID,
         Row_Number() over(partition by Employees.ID order by Site.ID) as RN
         from Employees
         Left Join Address on Employees.ID = Address.ID
         Left Join Site on Address.ADID = Site.ID 
         and Site.Type = 'Primary'
        ) as [AllEmpTable]
        Where [AllEmpTable].RN = 1
        Order by Employees.ID, Site.ID






        share|improve this answer








        New contributor




        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered 24 mins ago









        Isaac Holmes

        132




        132




        New contributor




        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        Isaac Holmes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.




















            Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.









             

            draft saved


            draft discarded


















            Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.












            Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.











            Isaac Holmes is a new contributor. Be nice, and check out our Code of Conduct.













             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218628%2fdisplay-one-row-over-another-if-there-are-two%23new-answer', 'question_page');

            );

            Post as a guest
































































            Comments

            Post a Comment

            Popular posts from this blog

            What does second last employer means? [closed]

            Image
            Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote -3 down vote favorite Say, for example I have worked in the following companies, Company A => 2010 - 2011 Company B => 2011 - 2012 Company C => 2012 - Till Date Company D => Have offer Now assume Company D is asking to submit documents from my second last employer. So from the list of companies given, which company would be qualified as second last employer? software-industry employer share | improve this question asked Aug 10 '14 at 5:16 Rajaprabhu Aravindasamy 559 1 6 13 closed as off-topic by Jim G., jmort253 ♦ Aug 11 '14 at 1:58 This question appears to be off-topic. The users who voted to close gave this specific reason: "Questions seeking advice on company-specific regulations, agreements, or policies should be directed to your manager or HR department. Questions that address...
            Read more

            Installing NextGIS Connect into QGIS 3?

            Image
            Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugins nextgis share | improve this question edited 3 hours ago PolyGeo ♦ 51.9k 17 77 233 asked 4 hours ago IanGS 16 4 New contributor IanGS is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. add a comment  |  up vote 1 down vote favorite Just acquired free version of NEXTGIS and trying to install the plugin for QGIS. It tells me it was not designed for version 3 of QGIS. How do I set up the QGIS-NEXTGIS interface then? qgis qgis-plugin...
            Read more

            One-line joke

            A one-liner is a joke that is delivered in a single line. A good one-liner is said to be pithy - concise and meaningful. [1] Comedians and actors use this comedic method as part of their act, e.g. Jimmy Carr, Tommy Cooper, Rodney Dangerfield, Norm Macdonald, Ken Dodd, Stewart Francis, Zach Galifianakis, Mitch Hedberg, Anthony Jeselnik, Milton Jones, Shappi Khorsandi, Jay London, Mark Linn-Baker, Demetri Martin, Groucho Marx, Dan Mintz, Emo Philips, Tim Vine, Steven Wright and Henny Youngman and Arnold Schwarzenegger. [ citation needed ] Many fictional characters are also known to deliver one-liners, including James Bond, who usually includes pithy and laconic quips after disposing of a villain. [ citation needed ] Examples "A baby seal walks into a club." "A dyslexic man walks into a bra." (Anonymous) "There are three types of people, those who can count and those who can't." "Venison's dear, isn't it?" (Jimmy Carr) "An escala...
            Read more