How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?
- 15 Answers
- 28331 Views
- Leo Technologies, Mannar Company, Synechron, I also Faced
- E-Mail Answers
Answer Posted / skybeaver
/* selecting duplicate rows in a table */
select col1, col2, ..., colN, count(*)
from TableName
group by col1, col2, ..., colN
having count(*) > 1
/* deleting duplicate rows from a table */
select col1, col2, ..., colN, count(*) as "Duplicates"
into #duplicates
from TableName
group by col1, col2, ..., colN
having count(*) > 1
delete TableName
from TableName t, #duplicates d
where t.col1 = d.col1 and
....
t.colN = d.colN
/* damn I'm good! */
| Is This Answer Correct ? | 11 Yes | 5 No |
Post New Answer View All Answers
Define a cross join?
How many types of dimensions are there and what are they? : sql server analysis services, ssas
Explain the steps needed to create a scheduled job?
Who developed sql server?
you want to be sure that queries in a database always execute at the maximum possible speed. To achieve this goal you have created various indexes on tables which other statement will keep the database in good condition? : Sql server administration
Can sql server be linked with other servers like oracle?
ow to bring suspect mode datbase online from scratch
Explain the difference between clustered and non-clustered index?
What are parameterized reports? What are cascading parameters in ssrs reports?
how will add additional conditions in sql?
what are the different types of replication you can set up in sql server? : Sql server database administration
How to get @@error and @@rowcount at the same time?
What is the maximum size of a row in sql server?
What is use of dbcc commands?
Using the customer, and order table in northwind database, please write a query to produce xml?
