oracle sql, id’s, group by one column, mulitple distinct in other column

I am trying to figure out how to write a query to do this. I have two ID’s in the table ID1 and ID2. Below is what I want.

Want

1. ID2 has count(ID2)>1
2. count of distinct ID1 >1

I am not sure how to do this with Oracle SQL. I did write code to do the group by. However, I am having trouble getting the rest of the code to work, including the part where the count of distinct ID1>1. I will post more code once I get more code done.

Data

ID1 ID2
1   33
1   33
5   44
6   44
7   8

Want

ID1 ID2
5   44
6   44

Attempt Code

select ID2, 
COUNT(*) 
from TABLE1 
GROUP BY ID2
HAVING COUNT(*)>1

Answer

If you want the entire rows, you can use window functions. Unlike several other databases, Oracle supports COUNT(DISTINCT ...) as a window function so that’s easy with:

select id1, id2
from (select t.*, count(distinct id1) over(partition by id2) cnt from mytable t)
where cnt > 1

Demo on DB Fiddle:

ID1 | ID2
--: | --:
  5 |  44
  6 |  44