Skip to content
Advertisement

SQL query to find out the single record by 3 conditions

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
                 );
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement