Menu
I have a table that contains unfortuantely bad data and I'm trying to filter some out. I am sure that the LName, FName combonation is unique since the data set is small enough to verify.
LName, FName, Email
----- ----- -----
Smith Bob [email protected]
Smith Bob NULL
Doe Jane NULL
White Don [email protected]
I would like to have the query results bring back the "duplicate" record that does not have a NULL email, yet still bring back a NULL Email when there is not a duplicate.
E.g.
Smith Bob [email protected]
Doe Jane NULL
White Don [email protected]
I think the solution is similar to Sql, remove duplicate rows by value, but I don't really understand if the asker's requirements are the same as mine.
Any suggestions?
Thanks
844
According to Delete Duplicate Rows in SQL, you can also use the SQL RANK feature to get rid of the duplicate rows. Regardless of duplicate rows, the SQL RANK function returns a unique row ID for each row. You need to use aggregate functions like Max, Min, and AVG to perform calculations on data.
RANK function to SQL delete duplicate rows We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row. In the following query, we use a RANK function with the PARTITION BY clause.
In the Navigation Pane, right-click the table that contains the field, and then click Design View. Select the field that you want to make sure has unique values. In the Field Properties pane at the bottom of the table design view, on the General tab, set the Indexed property to Yes (No duplicates).
You can use ROW_NUMBER() analytic function:
SELECT *
FROM (
SELECT a.*, ROW_NUMBER() OVER(PARTITION BY LName, FName ORDER BY Email DESC) rnk
FROM <YOUR_TABLE> a
) a
WHERE RNK = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
