弊社サポートセンターに、「IRIS SQLに Oracle の RANK() 関数のようものはありませんか?」というお問い合わせいただくことがあります。
IRIS2021.1以降のバージョンであれば、RANK() や ROW_NUMBER() などの [ウィンドウ関数](https://docs.intersystems.com/irislatest/csp/docbookj/DocBook.UI.Page.cls?KEY=RSQL_windowfunctions) がサポートされるようになりましたので、以下のように使用することができます。
// RANK() 関数
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">RANK</span>() <span class="hljs-keyword">OVER</span> (<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> Age) <span class="hljs-keyword">as</span> Ranking,<span class="hljs-keyword">Name</span>,Age
<span class="hljs-keyword">FROM</span> Sample.Person
<span class="hljs-keyword">WHERE</span> Age > <span class="hljs-number">60</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> Age
<colgroup><col style="width:63pt" width="84"><col style="width:130pt" width="173"><col style="width:44pt" width="58"></colgroup>
Ranking
|
Name
|
Age
|
1
|
Townsend,Neil W.
|
61
|
1
|
Murray,Terry J.
|
61
|
3
|
Huff,Patrick B.
|
67
|
4
|
Rotterman,Umberto A.
|
72
|
5
|
Quine,Imelda D.
|
75
|
6
|
McCormick,Imelda S.
|
80
|
7
|
Roentgen,Vincent Q.
|
81
|
8
|
Ueckert,Terry Q.
|
85
|
9
|
Perez,Ted P.
|
97
|
// ROW_NUMBER() 関数
<span class="hljs-keyword">SELECT</span> ROW_NUMBER() <span class="hljs-keyword">OVER</span> (<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> Age) <span class="hljs-keyword">as</span> Ranking,<span class="hljs-keyword">Name</span>,Age
<span class="hljs-keyword">FROM</span> Sample.Person
<span class="hljs-keyword">WHERE</span> Age > <span class="hljs-number">60</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> Age
<colgroup><col style="width:63pt" width="84"><col style="width:130pt" width="173"><col style="width:44pt" width="58"></colgroup>
Ranking
|
Name
|
Age
|
1
|
Townsend,Neil W.
|
61
|
2
|
Murray,Terry J.
|
61
|
3
|
Huff,Patrick B.
|
67
|
4
|
Rotterman,Umberto A.
|
72
|
5
|
Quine,Imelda D.
|
75
|
6
|
McCormick,Imelda S.
|
80
|
7
|
Roentgen,Vincent Q.
|
81
|
8
|
Ueckert,Terry Q.
|
85
|
9
|
Perez,Ted P.
|
97
|
* * *
IRIS2021.1より前のバージョンで同等のことを行いたい場合、[ビュー ID (%VID)](https://docs.intersystems.com/irislatest/csp/docbookj/DocBook.UI.Page.cls?KEY=GSQL_views#GSQL_views_vid) を使用してROW_NUMBER() 関数と同等のことが実現可能です。
%VID とは、ビューまたは FROM 節のサブクエリで返される各行に割り当てられる整数のIDです。
%VIDを使用することで、Order By クエリで並び替えた際に上位からIDを付与することが可能となります。
ただし、こちらの方法は同一値があった場合は同じ順位を付けることができないため、RANK() 関数ではなく、ROW_NUMBER() 関数と同等の機能となります。
実行例は以下のようになります。サブクエリには、TOP句を指定する必要があります。
// %VID を使用する方法
<span class="hljs-keyword">SELECT</span> %vid <span class="hljs-keyword">as</span> Ranking, *
<span class="hljs-keyword">FROM</span> (<span class="hljs-keyword">SELECT</span> top all <span class="hljs-keyword">Name</span>, Age
<span class="hljs-keyword">FROM</span> Sample.Person
<span class="hljs-keyword">WHERE</span> Age > <span class="hljs-number">60</span>
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> Age)
<colgroup><col style="width:63pt" width="84"><col style="width:130pt" width="173"><col style="width:44pt" width="58"></colgroup>
Ranking
|
Name
|
Age
|
1
|
Townsend,Neil W.
|
61
|
2
|
Murray,Terry J.
|
61
|
3
|
Huff,Patrick B.
|
67
|
4
|
Rotterman,Umberto A.
|
72
|
5
|
Quine,Imelda D.
|
75
|
6
|
McCormick,Imelda S.
|
80
|
7
|
Roentgen,Vincent Q.
|
81
|
8
|
Ueckert,Terry Q.
|
85
|
9
|
Perez,Ted P.
|
97
|
* * *
3rdパーティデータベース特有のコマンドをそのまま実行したい場合、直接パススルークエリを実行する方法もあります。
%Library.SQLGatewayConnection クラスを使用して直接クエリを実行する方法は以下のようになります。
<span class="hljs-keyword">set</span> dsn=<span class="hljs-string">"OracleDB"</span>
<span class="hljs-keyword">set</span> user=<span class="hljs-string">"scott"</span>
<span class="hljs-keyword">set</span> pass=<span class="hljs-string">"tiger"</span>
<span class="hljs-comment">// 接続</span>
<span class="hljs-keyword">set</span> cn=<span class="hljs-keyword">##class</span>(<span class="hljs-built_in">%SQLGatewayConnection</span>).<span class="hljs-built_in">%New</span>()
<span class="hljs-keyword">set</span> st=cn.Connect(dsn,user,pass)
<span class="hljs-comment">// SQL実行, Prepare第3引数に上記の接続変数を指定</span>
<span class="hljs-keyword">set</span> rs=<span class="hljs-keyword">##class</span>(<span class="hljs-built_in">%ResultSet</span>).<span class="hljs-built_in">%New</span>(<span class="hljs-string">"%DynamicQueryGW:SQLGW"</span>)
<span class="hljs-keyword">do</span> rs.Prepare(<span class="hljs-string">"select empno,sal,rank() over(order by sal) from emp"</span>,,cn)
<span class="hljs-keyword">do</span> rs.Execute()
<span class="hljs-comment">// 各行取得</span>
<span class="hljs-keyword">while</span> rs.Next() {
<span class="hljs-keyword">Write</span> !,rs.GetData(<span class="hljs-number">1</span>),<span class="hljs-string">" "</span>,rs.GetData(<span class="hljs-number">2</span>),<span class="hljs-string">" "</span>,rs.GetData(<span class="hljs-number">3</span>)
}
<span class="hljs-comment">// 切断</span>
<span class="hljs-keyword">do</span> cn.Disconnect()
詳細は以下のドキュメントをご覧ください。
[プログラムによる SQL ゲートウェイの使用法](https://docs.intersystems.com/irislatest/csp/docbookj/DocBook.UI.Page.cls?KEY=BSQG_prog)
【関連トピック】
[IRIS SQLでは OFFSET/LIMIT句のような機能をサポートしてますか?](https://jp.community.intersystems.com/node/531166)