duplicate value

sorry, u want duplicate values ?

what is cal_obj contain ?if it has duplicate values for this column in your table records, then it will display right otherwise not.

i what to get duplicate value and than delete tham

Thanks Slightwv
For Pointing me on Deleting Duplicate Records

Please Try This

SELECT FieldName, COUNT(*) TotalCount
  FROM [YourDB].[dbo].[YourTable]
GROUP BY FieldName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO

Select allOpen in new window

Then Use Following  To Delete ALL DUPLICATE RECORDS

WITH SimpleDLTE 
AS 
(
      SELECT ROW_NUMBER () OVER ( PARTITION BY YourField ORDER BY YourField) AS RNUM 
       FROM [YourDB].[dbo].[YourTable]
)

DELETE FROM SimpleDLTE WHERE RNUM > 1
GO

/* Your Details without Duplicates */
SELECT *   FROM [YourDB].[dbo].[YourTable]
GO

Select allOpen in new window

For Oracle
Try This

DELETE FROM YourTableOwner.YourTabel WHERE rowid not in (SELECT MIN(rowid) FROM YourTableOwner.YourTabel GROUP BY YourField)

Select allOpen in new window

>>For Oracle  Try This

Isn't that pretty much the same thing I posted in http:#a40559578

The only difference is 'not in' versus '>' and mine is a correlated subquery.

Try

delete from cal_obj where rowid in (
select rowid from (
select rowid,obj_name,
row_number () over (partition by obj_name order by rowid) as rid
from cal_obj) where rid <> 1);

Select allOpen in new window

crystal_Tech,

That is the worst one yet.

I agree that there are many ways to solve this problem.  There is no need to post them ALL.  I'm sure I could come up with a few more that are even worse performing that your latest one but why?

If there are indexes involved, and there probably are, the correlated subquery that I posted is very likely the most efficient.

thanks

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.