Find duplicate rows in SQL Server by multiple conditions

Question

My fields are

ID | Name | StartTime | EndTime | Date | Description

I am looking for a way to select all rows with the same entries in all fields except for the ID.

I am not that familiar with SQL so I tried this approach but there is only one field relevant not (as in my case) five.

My first idea was to try something like:

SELECT *
FROM Table
order by Name, Date, StartTime, EndTime, Description

if I would look through all entries I would at least find the duplicates but that is definitely not the best way to solve the problem.

score 2 · Accepted Answer · answered Nov 11 '14 at 07:40

2

This should do what you need:

select Name, Date, StartTime, EndTime, Description
from   table
group by Name, Date, StartTime, EndTime, Description
having count(*) > 1

answered Nov 11 '14 at 07:40

Jens

67,715
15
98
113

score 1 · Answer 2 · answered Nov 11 '14 at 07:36

This query should work for you:

SELECT ID, Name, StartTime, EndTime, Date, Description
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name, Date, StartTime, EndTime, Description) AS 'IndexNr'
        , ID
        , Name
        , StartTime
        , EndTime
        , Date
        , Description
    FROM Table) AS ResultSet
WHERE ResultSet.IndexNr > 1

score 1 · Answer 3 · answered Nov 11 '14 at 07:40

1

Try below query.

SELECT     Name, Date, StartTime, EndTime, Description
FROM    Table
GROUP BY Name, Date, StartTime, EndTime, Description
HAVING      (COUNT(*) > 1)

answered Nov 11 '14 at 07:40

user1089766

597
6
14

score -1 · Answer 4 · answered Nov 11 '14 at 07:36

-1

You can try this:-

SELECT *
FROM TAB_NAME
HAVING COUNT(CONCAT(Name, StartTime, EndTime, Date, Description)) > 1

This will give you all the rows that are repeated except than the ID.

answered Nov 11 '14 at 07:36

Ankit Bajpai

13,128
4
25
40

you can't use "HAVING" without "GROUP BY" – t-clausen.dk Nov 11 '14 at 09:12
@t-clausen.dk I think the query is executable, you must give it a try first. Having is only used when we have to put a aggregate function in the cond. It has no relation with the group by claue. you must refer some docs for this. – Ankit Bajpai Nov 11 '14 at 13:57
1

ok, I am refering to some docs now. Look at all the other answers. Also your syntax would be slow if it worked. If you had 2 rows with the values: null, null, null, 'A' and 'A', null, null, null they would be considered identical in this match. You can not use wildchar eather while using a group by. So everything is wrong. – t-clausen.dk Nov 11 '14 at 14:33

Find duplicate rows in SQL Server by multiple conditions

4 Answers4