distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?
How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com