CODEWITHSUNDEEP
sqlsql-server
Eryudes
Eryudes

Reputation: 11

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:

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

Upvotes: 0

Views: 315

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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;

Upvotes: 4

Related Questions