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.

Jens · Accepted Answer · 2014-11-11 07:40:24Z

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, 2014 at 7:40

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Radu Gheorghiu · Accepted Answer · 2014-11-11 07:36:37Z

1

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

answered Nov 11, 2014 at 7:36

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Comments

user1089766 · Accepted Answer · 2014-11-11 07:40:51Z

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, 2014 at 7:40

user1089766

6277 silver badges14 bronze badges

Comments

Ankit Bajpai · Accepted Answer · 2014-11-11 07:36:47Z

-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, 2014 at 7:36

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

3 Comments

t-clausen.dk Over a year ago

you can't use "HAVING" without "GROUP BY"

Ankit Bajpai Over a year ago

@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.

t-clausen.dk Over a year ago

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.

Collectives™ on Stack Overflow

Find duplicate rows in SQL Server by multiple conditions

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related