Distinct or group by on some columns but not others
I have a view that I'm trying to filter with something similar to DISTINCT on some columns but not others.
I have a view like this:
Name
LastName
Zip
Street1
HouseholdID (may not be unique because it may have multiple addresses -- think of it in the logical sense as grouping persons but not physical locations; If you lookup HouseholdID 4130, you may get two rows.. or more, because the person may have mutiple mailing locations)
City
State
I need to pull all those columns but filter on LastName,Zip, and Street1. Here's the fun part: The filter is arbitrary -- meaning I don't care which one of the duplicates goes away. This is for a mail out type thing and the other information is not used for any other reason than than to look up a specific person if needed (I have no idea why). So.. given one of the records, you can easily figure o开发者_开发知识库ut the removed ones.
As it stands now, my Sql-Fu fails me and I'm filtering in C# which is incredibly slow and is pretty much a foreach that starts with an empty list and adds the row in if the combined last name, zip, and street aren't are not in the list.
I feel like I'm missing a simple / basic part of SQL that I should be understanding.
Solution can be assuming we have Unique Identification column in view:
Select Name,
LastName,
Zip,
Street1,
HouseholdID,
City,
State
From View V1
INNER JOIN
(
Select Min([Unique Identification]) as ID
From View v
Group By LastName,
Zip,
Street ) AS T on T.ID = V1.[Unique Identification]
When No Identifier in View .......
Select Name,
LastName,
Zip,
Street1,
HouseholdID,
City,
State
From
(
Select Name,
LastName,
Zip,
Street1,
HouseholdID,
City,
State,
Row_Number() Over (Partition By LastName,Zip,Street Order By Street1,HouseholdID,City,State) AS RowNum
From View v
) AS T
Where T.RowNum = 1
精彩评论