WITH tabelle([Name],Vorname,Abteilung,Gehalt) AS (
SELECT 'NTest','VTest','Abt1',2500 UNION ALL
SELECT 'NTest1','VTest1','Abt1',2500 UNION ALL
SELECT 'NTest2','VTest2','Abt1',2800 UNION ALL
SELECT 'NTest3','VTest3','Abt2',2500 UNION ALL
SELECT 'NTest4','VTest4','Abt2',2500
), t AS (
SELECT ROW_NUMBER() OVER (PARTITION BY Abteilung ORDER BY count(*) DESC) AS zeile,
Abteilung,
Gehalt,
count(*) AS anzahl
FROM tabelle
GROUP BY Abteilung,Gehalt
)
SELECT tabelle.*
FROM tabelle
INNER JOIN t
ON tabelle.Abteilung = t.Abteilung
AND t.zeile = 1
WHERE tabelle.Gehalt != t.Gehalt