[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

duplicate rows

Hi

Can anyone give me a query to delete duplicate rows please.....something like delete where count(id)>1?

Cheers
0
ac_davis2002
Asked:
ac_davis2002
2 Solutions
 
Michael DyerCommented:
Try this:

delete from <Table>
where ID in
(SELECT MAX(ID) FROM TABLE
WHERE NAME IN (SELECT NAME FROM TABLE GROUP BY NAME HAVING COUNT(*)>1)
)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or this technique:

;with data as ( select d.*
   , row_number() over (partition by col1, col2 ... order by col3 ) rn
  from yourtable d
)
delete data
where rn > 1

Open in new window


all you need to specify is which column(s) define the "duplicates" (partition by) and which one(s) define the row to keep/delete (order by) in the row_number() function

see also this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
ac_davis2002Author Commented:
excellent
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now