最大値に付随する情報の取得方法

Question

前提

PostgreSQLの「名簿」というテーブルでフィールドが
｜ID番号｜所属部署｜氏名｜年齢｜性別｜というフィールドになっています。
所属部署ごとに、最も年齢が高い人が男性である場合、そのレコードを返すクエリを作りたいですが、
どうクエリを書いたらいいのかなすすべのない状態です。
googleで調べまくったのですが、これを実現する方法にたどり着けませんでした。

テーブルにはたとえば以下のレコードが入っていて
1,総務,山田太郎,30,男
2,総務,山田次郎,31,男
3,総務,山田三郎,32,男
4,総務,山田花子,31,女
5,経理,山田四郎,30,男
6,経理,山田佳子,34,女
クエリで返して欲しいのは
5,経理,山田四郎,30,男
のレコードです。
経理は女性が最高年齢なので抽出対象外です

該当のソースコード

select ID番号,所属部署,氏名,MAX(年齢),性別  from 名簿 GROUP BY 所属部署 HAVING 性別=男;

試したこと

上のコードまで書いて、これ以上なすすべがないです。
このコードでは明らかに間違いですが、これ以上なすすべがありません。
googleで調べまくったのですが、答えに結びつく情報に出会えませんでした。
すごく助けてほしい状態です。
お願いいたします。

Comments

[occhy]

教えていただいたコードでうまくいきました。
サブクエリが必要とは思いもしませんでしたが、論理上そうならざるをえないですね。
ありがとうございました。

Answer 1

所属部署ごとに、最も年齢が高い人が男性である場合、そのレコードを返す

このようなクエリで実現できるかと思います。

SELECT *
FROM (
  SELECT *, RANK() OVER(PARTITION BY 所属部署 ORDER BY 年齢 DESC) AS rnk
  FROM 名簿
) T
WHERE rnk = 1
AND 性別 = '男'