SQLで各カテゴリごとに上限数を変えてselectするには

自分へのメモ書き。あるカラムの値でグループに分類して、それぞれのグループから最大何個かずつ取得するのはROW_NUMBER()を使えば可能。これとCASEを使うことで、グループごとに取り出す数を変えることができる。

テーブル例:

create table Employee
(
    ID int NOT NULL primary key clustered,
    Name nvarchar(255) not null,
    OfficeId int not null
)

サンプルデータ:

insert into Employee (ID, Name, OfficeId) values
    (1, 'Mike', 0),
    (2, 'Andy', 1),
    (3, 'Nancy', 1),
    (4, 'Mary', 2),
    (5, 'John', 0),
    (6, 'Alice', 2),
    (7, 'Scott', 2)

ここから各OfficeId毎に1つずつエントリを取り出すには

SELECT e.* FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY OfficeId ORDER BY OfficeId) RN
    FROM Employee
) e
where RN < 2

こんな感じ。さらにOfficeId毎に取り出す上限数を変えるにはWHERE句にCASEを組み合わせればよい。

SELECT e.* FROM (
    select *, ROW_NUMBER() OVER(PARTITION BY OfficeId ORDER BY OfficeId) RN
    from Employee
) e
WHERE RN <= CASE OfficeId
    WHEN 0 THEN 3
    WHEN 1 THEN 2
    ELSE 1 END

これでOfficeIdが0のときは最大3件、1の時は最大2件、それ以外なら1件だけ取り出せる。ifとか変数とかは一切不要。