2番目をsqlで！知りたいのは上から１番目じゃないみたいです。

この記事は約 4 分で読めます。

このページではSQL文を書く上で上級者向けのテクニックです。

と言っても慣れてしまえばたいしたことはありません。大事な事は

そんなやり方があるんだ！！

という、その事実に気づくところです。

こういうのは普通だれも教えてくれないですからね。

上から２番目を求める事の難しさ
1. 集合関数での限界
2. row_numberとSelectした結果をSelectするやり方で上から２番目を求める。

上から２番目を求める事の難しさ

【激安特価品サーチ】

集合関数での限界

上から１番目を求める！

といった内容であれば真っ先に思いつくのが集合関数のMAXですね。これを使えば１番大きな値を求める事ができます。

そう

１番上の値しかだめなんですね・・・・・

また、以下のような表があったとします。

集合関数のMAXで求めれるのは

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

くだもの　→　5000円

さかな　　→　10000円

おにく　　→　500円

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

です。

でも、みんなが知りたいのって

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

くだもの　→　メロン（5000円）

さかな　　→　くえ（10000円）

おにく　　→　うま（500円）

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

って情報ですよね。

副問い合わせ等を使えば求められますが、書き方が非常にややこしくなります。

そこで、今回お伝えする上級テクニックは以前説明した以下の二つの内容を利用する事になります。

その５）SQLテクニック番号を振る（ROW_NUMBER、RANK、DENSE_RANK）

その２）SQLテクニック Selectした結果をSelectする

row_numberとSelectした結果をSelectするやり方で上から２番目を求める。

エクセルで作ったサンプルの方が分かりやすそうなのでそのままテーブルも作ってみました。

試してみたい人は以下をコピペしてテーブルを作ってください。

CREATE TABLE [dbo].[nedan](
[大項目] [nvarchar](50) NULL,
[中項目] [nvarchar](50) NULL,
[値段] [int] NULL,
[備考] [nvarchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[nedan](

[大項目] [nvarchar](50) NULL,

[中項目] [nvarchar](50) NULL,

[値段] [int] NULL,

[備考] [nvarchar](50) NULL

) ON [PRIMARY]

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

くだものリンゴ 100
くだものみかん 150
くだものスイカ 3000 ←二番目
くだものメロン 5000 ←一番高い
さかなさば 100
さかなくえ 10000 ←一番高い
さかなさんま 200 ←二番目
さかなさより 40
おにくぶた 150
おにくうし 300 ←二番目
おにくうま 500 ←一番高い

＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

※TSV形式になってます。

それではテーブルを見てみましょう。

ここにrow_number関数で大項目毎に連番を振ってみます。

文法は以下です。

row_number() over(partition by [大項目] order by [値段] desc ) as num

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT TOP (1000) [大項目]
      ,[中項目]
      ,[値段]
	  ,row_number() over(partition by [大項目] order by [値段] desc ) as num
      ,[備考]
  FROM [test001].[dbo].[nedan]

/****** SSMS の SelectTopNRows コマンドのスクリプト ******/

SELECT TOP (1000) [大項目]

,[中項目]

,[値段]

,row_number() over(partition by [大項目] order by [値段] desc ) as num

,[備考]

FROM [test001].[dbo].[nedan]

備考書いておいてよかったです。

説明が省けました。

各項目ごとに、２番目の値が一目でわかるようになりました。

あとは、この結果に対して

その２）SQLテクニック Selectした結果をSelectする

をするだけです。

クエリにするとこの様な感じです。

select * from
(
SELECT TOP (1000) [大項目]
      ,[中項目]
      ,[値段]
	  ,row_number() over(partition by [大項目] order by [値段] desc ) as num
      ,[備考]
  FROM [test001].[dbo].[nedan]
   )XX

select * from

(

SELECT TOP (1000) [大項目]

,[中項目]

,[値段]

,row_number() over(partition by [大項目] order by [値段] desc ) as num

,[備考]

FROM [test001].[dbo].[nedan]

)XX

ここまでは元の結果と同じですね。

架空のテーブルとして　XX　を定義しています。

このXXに対して、 XX.num=2 とする事で２番目に大きな値を求める事が出来ます。

クエリは此方です。

select * from
(
SELECT TOP (1000) [大項目]
      ,[中項目]
      ,[値段]
	  ,row_number() over(partition by [大項目] order by [値段] desc ) as num
      ,[備考]
  FROM [test001].[dbo].[nedan]
   )XX
where XX.num='2'

select * from

(

SELECT TOP (1000) [大項目]

,[中項目]

,[値段]

,row_number() over(partition by [大項目] order by [値段] desc ) as num

,[備考]

FROM [test001].[dbo].[nedan]

)XX

where XX.num='2'

見事に２番目の値だけを求める事ができました！

また、本当はみんなが欲しいであろう　中項目　の値もしっかりわかります。

この方法を利用すれば、

２番目に大きな値や２番目に少ない値なども取得できます。つまり応用の範囲が広いと言う事ですね。※asc descを切り替えることによって