How to Get Column Duplicates: Top SQL Example

You can easily find duplicates of a table using the SQL having and count function.

ON THIS PAGE

1. Create table and insert rows
2. Browse table data
3. SQL query with Group By and Having statements

Create table and insert rows

The Create table query creates table of my_table. It has two columns. One is root id, and the other one is hero_id. And inserted some rows into the table.

create table my_table(my_num number, root_id number, hero_id number);
insert into my_table
values
(
01, 1001, 2111);
insert into my_table
values
(
02, 1002, 2111);
insert into my_table
values
(
03, 1003, 2112);
select * from my_table;

Browse table data

Use SELECT query and get the table data. Look at the output, the hero_id contains duplicates. In the next query, you’ll see how to get these duplicates.

MY_NUM	ROOT_ID	HERO_ID
1	1001	2111
2	1002	2111
3	1003	2112

SQL query with Group By and Having statements

The hero_id contains duplicates. To get duplicates, use the having clause and Count>1.

select hero_id from my_table
group by hero_id
having count(*) > 1 ;

You can see in the output which value of hero_id has duplicates.

HERO_ID
2111

References

• Oracle 19C for Developers and Testers

Related