I wanted to a table like below :
Class_ID Student_ID Sub_ID
3171 10577752 1
3171 10577753 1
3171 10577753 2
3171 10577754 1
3171 10577755 1
3171 10577755 2
3172 10577756 1
3172 10577756 2
3172 10577756 3
3172 10577757 1
3172 10577757 2
3172 10577758 1
So I wanted to select the student_id that are in all the sub_id. For. eg : in class_id 3171 the total sub_id is 2(1 and 2) and the student_id 10577753 is there in sub_id 1 and 2 both but the id 10577752 is there in only the sub_id 1. so i wanted to select 10577753 and 10577755 for class_id 3171. Class_ID 2172 has the total sub_id 3(1,2,3) and the student_id 10577756 is there in all three. so i wanted to select only these student_id 10577753,10577755,10577756 from the above table.
Here's one option (sample data till line #26): temp CTE calculates number of distinct STU_IDs, partitioned by student (cnt_stu) or class (cnt_tot). Winners are students whose counts match.
SQL> WITH
2 test (class_id, student_id, sub_id)
3 AS
4 (SELECT 3171, 10577752, 1 FROM DUAL
5 UNION ALL
6 SELECT 3171, 10577753, 1 FROM DUAL
7 UNION ALL
8 SELECT 3171, 10577753, 2 FROM DUAL
9 UNION ALL
10 SELECT 3171, 10577754, 1 FROM DUAL
11 UNION ALL
12 SELECT 3171, 10577755, 1 FROM DUAL
13 UNION ALL
14 SELECT 3171, 10577755, 2 FROM DUAL
15 UNION ALL
16 SELECT 3172, 10577756, 1 FROM DUAL
17 UNION ALL
18 SELECT 3172, 10577756, 2 FROM DUAL
19 UNION ALL
20 SELECT 3172, 10577756, 3 FROM DUAL
21 UNION ALL
22 SELECT 3172, 10577757, 1 FROM DUAL
23 UNION ALL
24 SELECT 3172, 10577757, 2 FROM DUAL
25 UNION ALL
26 SELECT 3172, 10577758, 1 FROM DUAL),
27 temp
28 AS
29 (SELECT class_id,
30 student_id,
31 sub_id,
32 COUNT (DISTINCT sub_id)
33 OVER (PARTITION BY class_id, student_id) cnt_stu,
34 COUNT (DISTINCT sub_id) OVER (PARTITION BY class_id) cnt_tot
35 FROM test)
36 SELECT DISTINCT student_id
37 FROM temp
38 WHERE cnt_stu = cnt_tot
39 ORDER BY student_id;
STUDENT_ID
----------
10577753
10577755
10577756
SQL>