removing duplicated records

Hi,

Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.

I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.

Please advise,

Thanks,
MiraJwhy dont u post the DDL of table,some describtion abt table|||maybe you could use group by and use max() function to exclude duplicates

select col1, col2, max(text_col3), max(text_col4)...
from t
group by col1, col2...|||why dont u post the DDL of table,some describtion abt table

Table :

create table test(col1 int, col2 int, col3 text, col4 image)|||it is quiet difficult to do, there are some options,
insert all records into temp table ,add unique column before transfering data to temp table

1.If data values are less
than 8000 characters, you can cast the values to VARCHAR(8000) and use DISTINCT or
GROUP by to get the unique row.


If data values are greater than
8000
characters you may have to use a cursor & loop through
the rows & delete the
rows. In some cases you may have to use
an identity column or a number table
( with a cross join
operation ) to easily distingush one row from another.
----------
This link may help u http://www.windowsitpro.com/SQLServer/Article/ArticleID/23234/23234.html