How to Use SQL HAVING Clause Correctly in SQL Query

Here is SQL having clause example that shows how to get duplicate rows from a table.

I have created a Table and inserted values. One of the columns has duplicate values. I need to get duplicates. Below, you will find an example of how I achieved it using Having clause.

Table of contents

  1. Step#1: Created a Table and inserted values
  2. Step#2: Identified duplicates
  3. Step#3: Used Having clause to get duplicates

Step#1: Created a Table and inserted values

To create a Table, I used Create table query. Its name is my_table. It has two columns. One is root id, and the other one is hero_id.

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;

Step#2: Identified duplicates

The hero_id contains duplicates. And I need to get those.

MY_NUMROOT_IDHERO_ID
110012111
210022111
310032112

Step#3: Used Having clause to get duplicates

First, I got all hero_ids. Then, I used having count(*) > 1, which brings all the hero_ids. This way, you can correctly fetch the duplicates.


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

The output of duplicate Hero_ids

HERO_ID
2111

References

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.