Db2 : FETCH FIRST n ROWSとOFFSET、およびLIMIT代替構文（続き）-副選択で使う

前回の続きです。
[Db2 : FETCH FIRST n ROWSとOFFSET、およびLIMIT代替構文](https://qiita.com/camp07/items/60198248989774134a68)

Db2 11.1では、PostgreSQLやMySQLなどで使用されるLIMIT ... OFFSETといった代替構文も使用できるようになりました。

#副選択(subselect)でFETCH FIRSTとOFFSETを使う

FETCH FIRST/OFFSET節を使って SELECTでn行までしか取得しないように限定する書き方ですが、副選択(subselect)照会で使うこともできます。

こちらはDb2のSAMPLEデータベースのEMPLOYEE表を使っています。

例えば、
「３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前を知りたい」

という場合、（いろいろなアプローチが考えられますが）、その一つの例として以下のように書くことができます。




まず、各部門における平均サラリーを見てみます。



```
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC

```
```
WORKDEPT 2
-------- ---------------------------------
B01         94250.000000000000000000000000
E01         80175.000000000000000000000000
C01         77222.500000000000000000000000
A00         70850.000000000000000000000000
D11         58783.636363636363636363636363
D21         51240.000000000000000000000000
E21         47086.666666666666666666666666
E11         45305.714285714285714285714285

  8 レコードが選択されました。
```





「３番目にサラリー平均が高い部門の値」が欲しいので、FETCH FIRSTとOFFSETを使います。
つまり、２行スキップして３番目の値を取得します。

```
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT 
ORDER BY AVG(SALARY) DESC 
OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY
```
```
WORKDEPT 2
-------- ---------------------------------
C01         77222.500000000000000000000000

  1 レコードが選択されました。
```

これを副選択照会(subselect)に使って、３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前、をリストします。

```
SELECT LASTNAME, SALARY FROM EMPLOYEE 
	WHERE     
		SALARY > (       
			SELECT AVG(SALARY) FROM EMPLOYEE         
				GROUP BY WORKDEPT         
				ORDER BY AVG(SALARY) DESC         
				OFFSET 2 ROWS FETCH FIRST 1 ROW ONLY       
			)
ORDER BY SALARY
```

```
LASTNAME        SALARY
--------------- -----------
GEYER              80175.00
SPENSER            86150.00
HENDERSON          89750.00
THOMPSON           94250.00
PULASKI            96170.00
KWAN               98250.00
HAAS              152750.00

  7 レコードが選択されました。
```






#LIMIT ... OFFSETの代替構文を使ってみる

Db2 11.1では、PostgreSQLやMySQLなど他のデータベース製品で使用されるSQLも簡単にDb2環境で実行できるようにする機能拡張が含まれており、FETCH FIRST ... OFFSET節の代替として、シンプルにLIMIT/OFFSETを指定することもできるようになりました。




| 代替構文  |同等の構文 |
|:----------------|:--------------|
|LIMIT x          |  FETCH FIRST x ROWS ONLY |
|LIMIT x OFFSET y |OFFSET y ROWS FETCH FIRST x ROWS ONLY   |
|LIMIT y,x        |OFFSET y ROWS FETCH FIRST x ROWS ONLY   |




こちらを使って同じ結果を取得してみます。


```
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE 
GROUP BY WORKDEPT 
ORDER BY AVG(SALARY) DESC 
LIMIT 1 OFFSET 2
```
```
WORKDEPT 2
-------- ---------------------------------
C01         77222.500000000000000000000000

  1 レコードが選択されました。
```


それでは、３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前をリストします。
"LIMIT x OFFSET y"は、"LIMIT x,y"ではなく、"LIMIT y,x"であることに注意（！）


```
SELECT LASTNAME, SALARY FROM EMPLOYEE
	WHERE
		SALARY > (
			SELECT AVG(SALARY) FROM EMPLOYEE
				GROUP BY WORKDEPT
				ORDER BY AVG(SALARY) DESC
				LIMIT 2,1
			)
ORDER BY SALARY
```
```

LASTNAME        SALARY
--------------- -----------
GEYER              80175.00
SPENSER            86150.00
HENDERSON          89750.00
THOMPSON           94250.00
PULASKI            96170.00
KWAN               98250.00
HAAS              152750.00

  7 レコードが選択されました。

```

少しシンプルになりました。

#参考資料



- [Db2 11.1 Knowledge Center](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.welcome.doc/doc/welcome.html)

    - [fetch-first-clause](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059212.html)
    - [副選択照会の例](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059213.html)

    - [offset-clause](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061832.html)
    - [SQL の互換性の機能拡張](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.wn.doc/doc/c0054107.html)
        - FETCH FIRST 節の OFFSET オプション
        - 代替構文