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

Challenge: Best performing Query to find duplicates

I once a week challenge myself to find new ways of doing things.
Maybe u guys want to jump in on the game. ;)

Here ist the challenge.

You have to find a query that will return all duplicates in a Table, you have to use (*) in your select and it is not allowed to use group by!

Best performing, easiest to write query wins the price ;)
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with COUNT(*) OVER (PARTITION BY .... ) you can do it:

my article describes to get 1 record from the "duplicates" using the ROW_NUMBER() function, but basically it is the same technique:

in the end, you actually do a "group by", though implicitely.
however, you need to specify in the PARTITION BY which fields you actually want to be considered for checking the duplicates.
if you want to consider the whole row, you can do a partition using CHECKSUM(*):

that should be it what you are looking for
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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