Challenge: Best performing Query to find duplicates

Posted on 2012-09-07
Last Modified: 2012-09-23
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 ;)
Question by:T-Virus
    1 Comment
    LVL 142

    Accepted Solution

    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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now