Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • 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 ;)
0
T-Virus
Asked:
T-Virus
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with COUNT(*) OVER (PARTITION BY .... ) you can do it:
http://msdn.microsoft.com/en-us/library/ms189461.aspx

my article describes to get 1 record from the "duplicates" using the ROW_NUMBER() function, but basically it is the same technique:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

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(*):
http://msdn.microsoft.com/en-US/library/ms189788%28v=sql.90%29

that should be it what you are looking for
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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