How to find duplicate entries in the table

Question

I am trying to find duplicate entries in the table but if I use Code only in the script then it will give me the duplicate entries in the table.

But I want to get all the information from the table including duplicate code.

Can someone guide me where I am doing wrong?

This results in 2 entries:

SELECT code, COUNT(code)
FROM CodeDefinition
WHERE codingSystem = '2.16' AND code='835002601'
GROUP BY code
HAVING COUNT(code) > 1

But when I tried the below script, it returns nothing.

SELECT code, COUNT(code), description, shortdescription
FROM CodeDefinition
WHERE codingSystem = '2.16' AND code='835002601'
GROUP BY code, description, shortdescription
HAVING COUNT(code) > 1

duplicate rows

Please show some sample data and define what your duplicate criteria is — Dale K, Mar 08 '21 at 23:17
Does this answer your question? [Finding duplicate rows in SQL Server](https://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server) — Dale K, Mar 08 '21 at 23:20
Please don't use images, use formatted text. I've added a link to an answer which solves your problem. — Dale K, Mar 08 '21 at 23:22
While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Mar 08 '21 at 23:28

score 1 · Accepted Answer · edited Mar 08 '21 at 23:39

1

You are grouping by description which is causing the issue. Please try this.

    SELECT a.code, b.cnt , a.description, a.shortdescription
    FROM CodeDefinition a
    INNER JOIN (
        SELECT code, COUNT(code) cnt
        FROM CodeDefinition
        WHERE codingSystem = '2.16' AND code='835002601'
        GROUP BY code
        HAVING COUNT(code) > 1
    ) b ON a.code = b.code

edited Mar 08 '21 at 23:39

Dale K

25,246
15
42
71

answered Mar 08 '21 at 23:34

Raseena Abdul

516
3
6

1

Unfortunately, without a minimal reproducible example, such answers provide a diminished value. Nobody can re-run it, test it, learn from it, and apply for any scenario. – Yitzhak Khabinsky Mar 08 '21 at 23:43
Wouldn't an EXISTS be a better choice as far as performance? It would short circuit upon finding first duplicate, whereas using aggregation forces a table scan. – Anthony Hancock Mar 09 '21 at 00:08
@AnthonyHancock How will I get the count with exists? – Raseena Abdul Mar 09 '21 at 15:12
The OP didn't specify needing count (although yes it was in his example), just to find duplicates. But you can get the count with exists by using windowing functions COUNT(code) OVER (PARTITION BY Code). – Anthony Hancock Mar 09 '21 at 15:20
@AnthonyHancock Oh Yes. Thank you. I will update the answer. – Raseena Abdul Mar 09 '21 at 15:23
The EXISTS actually makes it WAY worse, don't do that after all :D – Anthony Hancock Mar 09 '21 at 15:30

score 0 · Answer 2 · answered Mar 09 '21 at 03:05

You can try window function with cte:

with cte as
(
    SELECT code, COUNT(code)over(partition by code) CodeCount, description, 
    shortdescription
    FROM CodeDefinition
    WHERE codingSystem = '2.16' AND code='835002601'
)
select code,max(codecount),description,shortdescription from cte where CodeCount>1
group by code,description, shortdescription

How to find duplicate entries in the table

2 Answers2