CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 7984

Select all matching records for a group

I have a table of Schools (in SQL Server)

each school has prioritised subject

School A :

1 : Physics
2 : Math
3 : English

School B:

1 : Math
2 : History

School C:

1 : Physics
2 : Math
3 : English
4 : History

School D:

1 : Physics
2 : Math
3 : English

I want to find the school that have the exact same priorities (in this case A and D)

My table look like this

ID  SchoolID  Pri_No  Subject
1   A         1       Physics
2   A         2       Math
3   A         3       English
4   B         1       Math
5   B         2       History
6   C         1       Physics
7   C         2       Math
8   C         3       English
9   C         4       History
10  D         1       Physics
11  D         2       Math
12  D         3       English

I built this query but it matches on the subject base not on the school level

SELECT  * FROM Schools A inner join Schools B on A.Pri_No=B.Pri_No and A.Subject=B.Subject and A.ID <> B.ID

Any idea how to achieve that?

Schools table called Unis

Unis look like this

SchoolID SchoolName
A        Melbourne
B        Sydney
C        Brisbane
D        Darwin

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One simple method uses string aggregation:

select subjects, string_agg(subject, ',') within group (order by schoolid) as schools
from (select schoolid,
             string_agg(subject, ',') within group (order by pri_no) as subjects
      from t
      group by schoolid
     ) s
group by subjects;

Upvotes: 1

Related Questions