Find Duplicate Records in a Table Using SQL Server

Finding duplicate values in a SQL table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on both of the columns.

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency":

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

• Recent PostgreSQL supports it.
• SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
• MySQL is unpredictable and you need sql_mode=only_full_group_by:
  
  
  • GROUP BY lname ORDER BY showing wrong results;
  • Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
• Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

SQL - How to get duplicate records from one table with join on another table

Without much information as to why 1000 - Value1000 should be the result instead of 1000 - Value9999, we can just get the first record from the top:

select top 1 * from (

-- your original query
SELECT a.AccessNumber, b.Value from Table1 a
JOIN Table2 b on b.AccessNumber = a.AccessNumber
Group by a.AccessNumber, b.Value
HAVING COUNT(1) > 1
-- your original query

) as x;

If we are just looking for the AccessNumber that has duplicate records, you can remove the b.Value from the selected columns and remove that as well in the Group By clause.

SELECT a.AccessNumber from Table1 a
JOIN Table2 b on b.AccessNumber = a.AccessNumber
Group by a.AccessNumber
HAVING COUNT(1) > 1;

http://www.sqlfiddle.com/#!18/3f114/3

Related Topics