CODEWITHSUNDEEP
sqloracle-database
vchitta
vchitta

Reputation: 2133

Get the Duplicated records with the matching IDs

I have a table called unique_table with data as below:

ID        NAME 
1         venkat 
2         subash 
3         venkat 
4         subash 
5         venkat 
6         subash 

I want to query the above table and get the out put as below:

ID        NAME      MATCHED_ID 
3         venkat    1 
4         subash    2             
5         venkat    1             
6         subash    2 

Upvotes: 0

Views: 73

Answers (3)

Alex Poole
Alex Poole

Reputation: 191245

As an alternative to xQbert's answer, you can also do this without the join by using analytic functions:

select id, name, matched_id
from (
    select id,
        name,
        first_value(id) over (partition by name order by id) as matched_id
    from unique_table
)
where id != matched_id
order by id;

       ID NAME   MATCHED_ID
---------- ------ ----------
         3 venkat          1 
         4 subash          2 
         5 venkat          1 
         6 subash          2

In the inner query, first_value() gives you the first ID (ie lowest number) for each name; the outer query then excludes the cases where a row matches itself - so only duplicates are listed, and the first appearance of each is not shown.

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Select T2.ID, T2.name, min(T1.ID) as matched_ID
FROM D t1
INNER JOIN D t2 on t1.ID < T2.ID 
and T1.name = T2.name 
Group by T2.name, T2.ID
order by ID


create table D as (
Select 1 as ID ,'venkat' as name from dual UNION
SELECT 2,'subash' from dual UNION 
SELECT 3,'venkat' from dual UNION
SELECT 4,'subash' from dual UNION
SELECT 5 ,'venkat' from dual UNION
SELECT 6,'subash' from dual)

Results in 

ID      Name    Matched ID
3       venkat  1
4       subash  2
5       venkat  1
6       subash  2

Upvotes: 1

Frank LaPoint
Frank LaPoint

Reputation: 11

SELECT YourColumn, COUNT(*) MatchedCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

http://blog.sqlauthority.com/2007/07/11/sql-server-count-duplicate-records-rows/

Upvotes: 1

Related Questions