• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

duplicate value

hi how can i get duplicate value in this sql

select obj_name from cal_obj
0
chalie001
Asked:
chalie001
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
sorry, u want duplicate values ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is cal_obj contain ?if it has duplicate values for this column in your table records, then it will display right otherwise not.
0
 
chalie001Author Commented:
i what to get duplicate value and than delete tham
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
Try this:

delete from cal_obj a where rowid> (select min(rowid) from cal_obj b where  a.obj_name=b.obj_name);
0
 
crystal_TechCommented:
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

Open 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

Open in new window

0
 
crystal_TechCommented:
For Oracle
Try This

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

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
crystal_TechCommented:
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);

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
chalie001Author Commented:
thanks
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now