January 22, 2010 at 12:13 pm
Hello
i have a table called "BlockInfo" with below columns
----> Name,City,State,Pin,
i woulf like to display duplicate rows
but the trick here is, i have to check if Name and City is Duplicated then the row is duplicated or if pin no is duplicated then whole record is assumed as duplicated
if just Name and City columns is Duplicated then the record is duplicated {OR} if pin no is duplicated then whole record is assumed as duplicated
how can i show (or write a query) to display all duplicated values (with satisfying above 2 conditions)
Please Help me
Thanks in advance
asita
January 22, 2010 at 1:54 pm
From your description I assume that Pin is supposed to be unique ? therefore using it as Key
This is not the most elegant solution, but it should return what you've requested
select * from Blockinfo
where pin
in (
Select max(pin) as PINKey
from Blockinfo
group by Name,City
having count(1) > 1
union all
select pin
from Blockinfo
group by Pin
having count(1) > 1
)
January 22, 2010 at 2:13 pm
Would you mind to provide table def, some sample data and expected result? Please have a look at the first article in my signature on how to do it the most efficient way.
There are some people on this forum (including me) that prefer to work with ready to use data instead of verbal descriptions...
January 22, 2010 at 3:18 pm
Since you have two different criteria for deleting may I suggest first deleting using the Pin values for which you could use a CTE such as:
;with numbered as(SELECT rowno=row_number() over
(partition by Pin order by Pin), Name,City,State FROM PartDemo)
select * from numbered
to delete those with duplicate PIN values - advise you to run the code with the SELECT statement to verify that the result are what you want. Remember to Test, Test and Test again before using in production
once you are sure this is correct then replace the SELECT statement with Delete from numbered when rowno > 1
You can modify the above CTE to (partition by name, city Order by Pin )... to identify those duplicate rows and follow the same comments for the their deletion.
Without sample data, table definition this has not been tested so again test, test, test again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply