Wypisanie wartości maksymalnej dla każdego stanowiska

Cześć,
Mam pewien problem, nad którym siedzę już kilka dni. Muszę wypisać nazwy departamentów oraz stanowiska, na których pracuje najwięcej osób w danym departamencie. Stworzyłam tabelę, która zwraca nazwę departamentu, stanowisko oraz liczbę osób pracujących na danym stanowisku w danym departamencie:

Select nr_departamentu, nazwa, stanowisko, count(id_pracownika)
from pracownik p, departament d
where p.nr_departamentu = d.nr_departamentu
group by d.nr_departamentu, nazwa, stanowisko

W wyniku otrzymałam tabelę :

Nr_departamentu | Nazwa |Stanowisko| count(id_pracownika)
1 | Departament 1 |Analityk| 4
1 | Departament 1 |Księgowy| 2
2 | Departament 2 |XYZ| 8
2 | Departament 2 |Prezes| 1

W wyniku powinnam otrzymać:
Nr_departamentu | Nazwa |Stanowisko| count(id_pracownika)
1 | Departament 1 |Analityk| 4
2 | Departament 2 |XYZ| 8

W jaki sposób mam wyciągnąć informację o maksimum dla każdego departamentu..?

Nie podałaś co to za baza więc ciężko podać gotowca ale prócz group by powinno być jeszcze coś takiego jak having przykład z postgresql:
https://www.postgresqltutorial.com/postgresql-having/
Prócz tego można spróbować z podselectem itd. pomysłów jest kilka na takie rozwiązanie

Jeśli Ci się chce to przygotuj przykład na
http://sqlfiddle.com/
a spróbujemy Ci gotowca dać :)

@woolfik: W takim razie wrzucam Ci screen bazy (mam nadzieję, że wystarczy) oraz dokładną treść zadania :)

Zadanie: Dla każdego departamentu podać jego nazwę oraz stanowiska, na których pracuje najwięcej ludzi w tym departamencie.

@woolfik baza oracle

@woolfik: Jesteś w stanie prosto wytłumaczyć mi jak działa funkcja rownum?

SELECT nr_departamentu
	,nazwa
	,stanowisko
	,max(cnt)
FROM (
	SELECT nr_departamentu
		,nazwa
		,stanowisko
		,count(id_pracownika) cnt
	FROM pracownik p
		,departament d
	WHERE p.nr_departamentu = d.nr_departamentu
	GROUP BY d.nr_departamentu
		,nazwa
		,stanowisko
	) XXX
GROUP BY nr_departamentu
	,nazwa
	,stanowisko

Dobra tak będzie dobrze:

select x.stanowisko, d.nazwa, x.ile from (
select s.stanowisko, s.nr_departamentu, s.ile,
       dense_rank() over (partition by s.nr_departamentu order by s.ile desc) dd
  from 
(select stanowisko, nr_departamentu,  count(*) ile
  from pracownik 
 group by stanowisko, nr_departamentu)s 
  ) x
  join departament d on d.nr_departamentu = x.nr_departamentu
 where x.dd = 1;

http://sqlfiddle.com/#!4/db7382/55