Find duplicate values in oracle
I am using this query to find duplicate values in a table:
select col1,
count(col1)
from table1
group by col1
having count (col1) > 1
order by 2 desc;
But also I want to add another column from the same table, for example:
select col1,
col2,
count(col1)
from table1
group by col1
having count (col1) > 1
order by 2 desc;
I am getting error ORA-00979
with this second request
How can I add another column to my search?
source to share
Presumably you want to get col2
for every duplicate col1
that appears. You cannot do this in one request ^. Instead, you need to get a list of duplicates and then use them to get any other related values:
select col1, col2
from table1
where col1 in (select col1
from table1
group by col1
having count (col1) > 1)
order by col2 desc
^ Ok, you can using analytic functions like @rs. demonstrated. For this scenario, I suspect the subquery will be more efficient, but both should give you the same results.
Based on the comments, it seems that it is not clear to you why you cannot just add a second column. Suppose you have sample data that looks like this:
Col1 | Col2
-----+-----
1 | A
1 | B
2 | C
2 | D
3 | E
If you run
select Col1, count(*) as cnt
from table1
group by Col1
having count(*) > 1
then your results will be as follows:
Col1 | Cnt
-----+-----
1 | 2
2 | 2
You can't just add col2
to this query without adding it to the clause group by
, because the database will have no way of knowing what value you really want (i.e. for Col1 = 1 if DB returns A 'or' B '?) ... If you add Col2 to the offer group by
, you get this:
select Col1, Col2, count(*) as cnt
from table1
group by Col1, Col2
having count(*) > 1
Col1 | Col2 | Cnt
-----+------+----
[no results]
This is because the counter is for every combination of col1
and col2
(each is unique).
Finally, using either a nested query (like in my answer) or an analytic function (like in @rs .'s answer), you will get the following result (the query has changed slightly to return the score):
select t1.col1, t1.col2, cnt
from table1 t1
join (select col1, count(*) as cnt
from table1
group by col1
having count (col1) > 1) t2
on table1.col1 = t2.col1
Col1 | Col2 | Cnt
-----+------+----
1 | A | 2
1 | B | 2
2 | C | 2
2 | D | 2
source to share
Cause of error
You tried to execute an SQL SELECT statement that included a GROUP BY (i.e. SQL MIN function, SQL MAX function, SQL SUM function, SQL COUNT function) and an expression in a SELECT list that was not in the SQL GROUP BY clause.
select col1,
col2,
count(col1)
from table1
group by col1,col2
having count (col1) > 1
order by 2 desc;
source to share