Oracle SQL: find duplicated row

There are a couple ways to do this basically around joining the table to itself. Here's one solution using a common table expression with the rank() function:

with cte as ( select itemId, name, partnum, price, rank() over (order by name, partnum, price) rnk from yourtable ) select distinct c.* from cte c join cte c2 on c.rnk = c2.rnk and c.itemid != c2.itemid 

SQL Fiddle Demo

Here's an alternative approach:

select distinct y.* from yourtable y join yourtable y2 on y.name = y2.name and y.partnum = y2.partnum and y.price = y2.price and y.itemid != y2.itemid 

More Fiddle

Now I understand and you can do this:

select * from yourTable where name in ( select name from ( SELECT Name, PartNum, Price, count(ItemId) qtd FROM yourTable group by Name, PartNum, Price,) where qtd>1) 

Claudio's answer is pretty close, but to filter the results based on the number of duplicates, you'll want to add a having clause:

select name, partnum, price from yourTable group by name, partnum, price having count(itemId) > 1 

This is another approach :

Query:

select * from Table1 where Name||','||PartNum in ( select Name||','||PartNum from Table1 group by Name, PartNum having count(*) > 1) 

Results:

| ITEMID | NAME | PARTNUM | PRICE | |--------|-------|---------|-------| | 1 | apple | 123 | 0.99 | | 3 | apple | 123 | 0.99 | 

This is another alternative

SELECT t1.itemId, t1.name, t1.partNum, t1.price FROM table1 t1 INNER JOIN (SELECT name, partNum, price, COUNT(*) AS count FROM table1 GROUP BY name, partNum, price HAVING COUNT(*) > 1 ) dt ON t1.name = dt.name and t1.partNum = dt.partNum and t1.price = dt.price ORDER BY t1.itemId 

Check it on SQL FIDDLE

Here's another method with fiddle. This uses the analytic function count(*)

select itemid,name,partnum,price from ( select itemid, name, partnum, price, count(*) over (partition by partnum order by price) as part_count from yourtable ) where part_count >1