The Easy Way to Get Duplicate Records: Top SQL Query

Here is an SQL query helpful to get duplicate records. This query shows how to use having and count functions for this purpose.

ON THIS PAGE

1. Create a table and insert rows
2. Read Table data
3. SQL query to get duplicate records

Create a 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;

Read 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 to get duplicate records

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