Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    85

    Unanswered: 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,
    MiraJ

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Question

    why dont u post the DDL of table,some describtion abt table
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    maybe you could use group by and use max() function to exclude duplicates
    Code:
    select col1, col2, max(text_col3), max(text_col4)...
    from t
    group by col1, col2...

  4. #4
    Join Date
    Jun 2005
    Posts
    85
    Quote Originally Posted by mallier
    why dont u post the DDL of table,some describtion abt table
    Table :

    create table test(col1 int, col2 int, col3 text, col4 image)

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    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/SQLServe...234/23234.html
    Last edited by mallier; 12-02-05 at 08:45.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •