Nana Hemaa
asked on
SQl Duplicate query in Oracle
How can I accomplish below with SQl Developer--for Oracle database 11 G
;With CTE As
(Select Cust_ID, name, Results, Date, Row_number() Over(PARTITION By Cust_ID + Name + Results Order By Date Desc) As Cust
From Customer)
Delete From CTE
Where Cust >1
;With CTE As
(Select Cust_ID, name, Results, Date, Row_number() Over(PARTITION By Cust_ID + Name + Results Order By Date Desc) As Cust
From Customer)
Delete From CTE
Where Cust >1
try
;With dup as (
Select Cust_ID, name, Results, rowid rid, Date,
Row_number() Over(PARTITION By Cust_ID, Name, Results Order By Date Desc) as rn
From Customer
)
Delete From Customer
Where rowid in (select rid fom dup where rn>1)
Huseyin,
Have you tried a delete using CTE in Oracle?
Have you tried a delete using CTE in Oracle?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
this deletes everything that has the same cust_id and results. If more columns define uniqueness, just add them.