CODEWITHSUNDEEP
sqldatabaseduplicatessybase
rkyyk
rkyyk

Reputation: 163

Sybase removing duplicate data

I have noticed that a table in my database contains duplicate rows. This has happened on various dates.

When i run this query 

 select  ACC_REF, CIRC_TYP, CIRC_CD, count(*) from table

 group by ACC_REF, CIRC_TYP, CIRC_CD

 having count(1)>1

I can see the rows which are duplicated and how many times it excists (always seems to be 2).

The rows do have a unique id on them, and i think it would be best to remove the value with the newest id

I want to select the data thats duplicated but only with the highest id so i can move it to another table before deleteing it.

Anyone know how i can do this select?

Thanks a lot

Upvotes: 1

Views: 6337

Answers (4)

Boggio
Boggio

Reputation: 1148

It will only output the unique values from your current table along with the criteria you specified for duplicate entries.

This will allow you to do one step "insert into new_table" from one single select statement. Without having to delete and then insert.

select 
         id
        ,acc_ref
        ,circ_typ
        ,circ_cd
from(
      select
             id
            ,acc_ref
            ,circ_typ
            ,circ_cd
            ,row_number() over ( partition by 
                                             acc_ref
                                            ,circ_typ
                                            ,circ_cd
                                order by id desc
                                ) as flag_multiple_id
      from Table
    ) a
where a.flag_multiple_id = 1 -- or > 1 if you want to see the duplicates

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44336

select  max(id) as maxid, ACC_REF, CIRC_TYP, CIRC_CD, count(*) 
from table
group by ACC_REF, CIRC_TYP, CIRC_CD
having count(*)>1

Edit:

I think this is valid in Sybase, it will find ALL duplicates except the one with the lowest id

;with a as
(
select  ID, ACC_REF, CIRC_TYP, CIRC_CD, 
row_number() over (partition by ACC_REF, CIRC_TYP, CIRC_CD order by id) rn, 
from table
)
select ID, ACC_REF, CIRC_TYP, CIRC_CD
from a
where rn > 1

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21667

Try something like this:

SELECT t1.*
FROM YOURTABLE t1
INNER JOIN (
  SELECT max(id) ID,
    ACC_REF,
    CIRC_TYP,
    CIRC_CD
  FROM YOURTABLE 
  GROUP BY ACC_REF,
    CIRC_TYP,
    CIRC_CD
  HAVING COUNT(id) > 1
  ) t2 ON t2.id = t1.id;

Upvotes: 0

Sanal K
Sanal K

Reputation: 733

Try like this

SELECT t1.* FROM table t1, table t2 WHERE t1.id < t2.id AND t1.ACC_REF = t2.ACC_REF AND t1.CIRC_TYP = t2.CIRC_TYP AND t1.CIRC_CD = t2.CIRC_CD

Upvotes: 0

Related Questions