none
Select row with the lowest value in column

    Question

  • I'm using sql express 2005.  I need help with buliding a view.

     

    I'm trying to join two tables and then, if duplicate ID's exist, retrieve the row with the lowest value in a specific column.

     

    For instance:

     

    TableHousehold:

    ID         Name                Status

    001       The Smith’s       A

    002       The Jone’s         A

    003       The Adam’s       A

    004       The Grant’s       A

     

    TableResidents

    Pk        HouseholdID      ResidentID        Name                Gender 

    1          001                   1                      Mary Smith       F

    2          001                   2                      Jason Smith      M

    3          001                   3                      Rachel Smith     F

    4          002                   1                      Mike Jones        M

    5          002                   2                      Sara Jones        F

    6          003                   2                      Erik Adams       M

     

    The resident ID is auto increment and assigned by the server.  It is possible for resident 1 for each household to have been deleted.  It is also possible that a household does not contain any residents.  Therefore, I wish to join these tables and return this:

     

    ID         HouseHoldName            Status               ResidentName   Gender

    001       The Smith’s                   A                      Mary Smith       F

    002       The Jone’s                     A                      Mike Jones        M

    003       The Adam’s                   A                      Erik Adams       M

    004       The Grant’s                   A                      null                   null

     

    There are other columns in both tables that I will want to join, but for simplicity, the above example illustrates my need.

     

    I was able to accomplish this by creating two views.  But I’d like to try and do this in one.

     

    Thursday, December 20, 2007 7:13 PM

Answers

  • Hi,

    Try the below query,

    Code Snippet

    SELECT DISTINCT(a.id),a.name,a.status,b.name AS ResidentName,b.Gender FROM TableHousehold a
    LEFT OUTER JOIN TableResidents b ON a.id=b.HouseholdID
    AND b.ResidentID = (SELECT MIN(ResidentID) FROM TableResidents WHERE HouseholdID = b.HouseholdID)


    Friday, December 21, 2007 1:25 AM
    Moderator

All replies

  • Hi,

    Try the below query,

    Code Snippet

    SELECT DISTINCT(a.id),a.name,a.status,b.name AS ResidentName,b.Gender FROM TableHousehold a
    LEFT OUTER JOIN TableResidents b ON a.id=b.HouseholdID
    AND b.ResidentID = (SELECT MIN(ResidentID) FROM TableResidents WHERE HouseholdID = b.HouseholdID)


    Friday, December 21, 2007 1:25 AM
    Moderator
  • Perfect!  Thank you.

     

    Saturday, December 22, 2007 6:57 AM