CODEWITHSUNDEEP
sql-serversql-server-2008
IUnknown
IUnknown

Reputation: 9819

How to list duplicate records?

I have the following table structure:

id|date|studenttypeid|name|audituser
1|.....|4|Jason|....
2|.....|4|Robin|....
3|.....|4|Jason|....
4|.....|4|Dan|....
5|.....|4|Robin|....

I need to list all records which are duplicates on studenttypeid + name.

With the above data, the query should give me the following output:

1|.....|4|Jason|....
2|.....|4|Robin|....
3|.....|4|Jason|....
5|.....|4|Robin|....

How can I achieve this on SQL Server 2008?

Upvotes: 0

Views: 51

Answers (4)

PP006
PP006

Reputation: 709

Try this

SELECT a.*
FROM   yourtable a
JOIN   (SELECT studenttypeid,
               name
        FROM   yourtable
        GROUP  BY studenttypeid,
                  name
        HAVING Count(studenttypeid) > 1) b ON b.name = a.name
ORDER  BY a.id

Upvotes: 0

Fiddles
Fiddles

Reputation: 2915

If you want to include every occurrence of the duplicates, some more methods using CROSS APPLY...

SELECT p.*
FROM people p
CROSS APPLY (
    SELECT TOP(1) * FROM people p2 WHERE p2.ID <> p.ID AND p2.name = p.name AND p.studenttypeid = p2.studenttypeid
) as pWithDups

Or an EXISTS check

SELECT p.*
FROM people p
WHERE EXISTS (
    SELECT * 
    FROM people p2 
    WHERE p2.ID <> p.ID AND p2.name = p.name AND p.studenttypeid = p2.studenttypeid
)

Upvotes: 0

Greg the Incredulous
Greg the Incredulous

Reputation: 1846

You can use a group by and then join back to the original table, like this:

WITH Temp(StudentTypeId, Name) AS(
    SELECT 
        StudentTypeId, Name
    FROM YourTable
    GROUP BY 
        StudentTypeId, Name
    HAVING Count(1) > 1
)

SELECT YourTable.*
FROM YourTable
INNER JOIN Temp
    ON YourTable.StudentTypeId = Temp.StudentTypeId
    AND YourTable.Name = Temp.Name

Upvotes: 1

John Woo
John Woo

Reputation: 263733

you can use ROW_NUMBER()

SELECT ID, DATE, studenttypeid, name, audituser
FROM
(
    SELECT ID, DATE, studenttypeid, name, audituser,
           ROW_NUMBER() OVER (PARTITION BY studenttypeid, name
                              ORDER BY id) rn
    FROM   yourTableName
) a
WHERE  rn = 1

Upvotes: 0

Related Questions