Select 3 and more duplicate rows in SQL Server

I’m learning the SQL Server and I got confused while selecting 3 and more duplicate rows in the SQL Server. I need to filter my data minimum 3 and more duplicate rows by using the SQL Server and I don’t know how to select them.

The table structure is:

• Date
• Hour
• ID
• WHO
• ItemNAME

I want to see 3 and more duplicate rows in Hour and ID at the same time. Could you please help me guys?

Answer

You can get hour/id pairs with such duplicates as:

select hour, id
from t
group by hour, id
having count(*) >= 3;

If you want the original rows, there are several methods. One uses window functions:

select t.*
from (select t.*, count(*) over (partition by hour, id) as cnt
      from t
     ) t
where cnt >= 3;