can i select duplicate rows in a table -how? 8

[database1]

how can i select duplicate records in a table

 

[stan123]

This will let you find all rows that contain duplicate records for certain column(s)<br>(helpfull if you try to build a pk on a table which already contains data).<br><br>select count(*), col1, col2... coln<br>from table_name<br>group by col1, col2... coln<br>having count(*) &gt; 1<br><br>This will let you get rid of duplicate rows in a table.<br>MAKE A BACKUP FIRST <br><br>--store all distinct records in a separate table<br>select distinct *<br>into #temp_table<br>from table_name<br><br>--empty the original table<br>truncate table_name<br><br>-- reinsert records from #temp_table back to the original <br>--one<br>insert table_name<br>select * from #temp_table<br><br>Hopefully that will help.<br>

 

[cjackson]

I need to delete a large amount of duplicate rows from a table before my managment team discover the sales figures are not as good as the report tells them..

I have tried using the code below but it does not seem to work

select distinct *
into #temp_table
from table_name

If I then query the #temp_table it still has the duplicate rows.

Am I missing something basic?

Thanks in advance for any help

 

[jballum]

When you use 'Select distinct *' it will look at every single field for each record for distinctness.

Is it possible that there is a field in each record that makes it distinct?

Identity field?
Timestamp field?

Just a thought.

JB

 

[cjackson]

Thanks for that I think your right, there are two dates in the table both set to smalldatetime.

I need to preserve the dates but I am not bothered about the times, whats the best way of achieving this?

CJ

 

[jballum]

select
field1,
field2,
...... ,
max(smalldatetime1), /* or min */
max(smalldatetime2) /* or min */
into
#temptable
from
tablename
group by field1, field2, ..... /* not smalldatetime fields */

This should give you the distinct records (if it was the smalldatetime fields causing the differences) that you need.

JB

 

[cjackson]

JB,

I tried the code you sugested but get the following error when I try and execute it: -

Server: MSG 8155, Level 16, State1,Line1
No column was specified for column x of '#table'

x appears to correspond to the column where the max(smalldatetime) fields should be.

I tried to look this error up in books online and Technet but did not find any solutions. Anyone else help?

Thanks in advance.

CJ

 

[cdukes]

Try putting a column name for the two calculated
fields as SQL server does not know what to call them in the
#temptable:

select
field1,
field2,
...... ,
max(smalldatetime1) as Maxdate1, /* or min */
max(smalldatetime2) as Maxdate2 /* or min */
into
#temptable
from
tablename
group by field1, field2, .....

Hope this helps,


Chris Dukes

 

[jballum]

Oops sorry about that. Thanks for correcting my mistake cdukes.

 

[cjackson]

Thanks everyone this is working great now, turned out there was a mixture dates and null values causing the problem.

I've got index's on now so the next problem will be a new adventure...

 

[cjackson]

Thanks everyone this is working great now, turned out there was a mixture dates and null values causing the problem.

I've got index's on now so the next problem will be a new adventure...