前回の記事(Firebirdで問い合わせの結果に連番を振る)の続き。
ROW_NUMBER()関数はPARTITION BYで、連番を振る範囲を指定できます。
Firebirdで同じことをするにはどうしたらいいか考えました。
■テーブル定義とサンプルデータ
CREATE TABLE classmate (
name VARCHAR(10),
class INTEGER,
score INTEGER
);
INSERT INTO classmate(name,class,score) VALUES ('野比 のび太', 1, 20);
INSERT INTO classmate(name,class,score) VALUES ('源 静香', 1, 80);
INSERT INTO classmate(name,class,score) VALUES ('骨川 スネ夫', 2, 60);
INSERT INTO classmate(name,class,score) VALUES ('剛田 武', 1, 40);
INSERT INTO classmate(name,class,score) VALUES ('出木杉 英才', 2, 100);
■class別にscoreの高い順に連番を振るSQL
SELECT
rdb$get_context('USER_TRANSACTION', 'ranking_no') AS RANKING,
rdb$set_context('USER_TRANSACTION', 'class_no', classmate.class),
rdb$set_context('USER_TRANSACTION', 'ranking_no',
CASE
WHEN COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'class_no') AS INTEGER), 0) = classmate.class THEN CAST(rdb$get_context('USER_TRANSACTION', 'ranking_no') AS INTEGER) + 1
ELSE 1 END),
name,
class,
score
FROM classmate
ORDER BY class, score DESC
■実行結果
RANKING | RDB$SET_CONTEXT | RDB$SET_CONTEXT1 | NAME | CLASS | SCORE |
---|---|---|---|---|---|
1 | 0 | 0 | 源 静香 | 1 | 80 |
2 | 1 | 1 | 剛田 武 | 1 | 40 |
3 | 1 | 1 | 野比 のび太 | 1 | 20 |
1 | 1 | 1 | 出木杉 英才 | 2 | 100 |
2 | 1 | 1 | 骨川 スネ夫 | 2 | 60 |
一見、正しく動いているように見えますが、どうでしょうか。