How to find duplicate entries in the table

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

Answer

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