Can you please help me with the query to find out the single record for the each student based on the subject.(Id,Name,subs are primary key). Ideally I will have 2 records for each subject. One with school TTT and MCC.
Table Structure:
StudentID StudentName Subject Marks School 1 AAA ENG 80 TTT 1 AAA ENG 80 MCC 1 AAA TAM 90 TTT 1 AAA TAM 90 MCC 2 BBB TAM 90 TTT 2 BBB TAM 90 MCC 3 CCC ENG 40 MCC 4 DDD ENG 95 MCC 5 EEE ENG 85 TTT 5 EEE ENG 85 MCC
I want the results
StudentID StudentName Subject Marks School 3 CCC ENG 40 MCC 4 DDD ENG 95 MCC
Advertisement
Answer
Here is one method using window functions:
select t.* from (select t.*, count(*) over (partition by studentid) as cnt from t ) t where cnt = 1;
This returns students with only one record. If you want student/subject combination with only one record, another method uses not exists
:
select t.* from t where not exists (select 1 from t t2 where t2.studentid = t.studentid and t2.subject = t.subject and t2.school <> t.school );