最新の日付と2番目の日付のデータ取得方法

SQL Server 2008 にて下記条件を満たしたデータの取得を行いたいです。

・IDごとに、日付がある日以前で最新のものとその次の日付のデータ2件
・ただし、その2件で価格が変わらなければ価格変動のあった日付のデータまでさかのぼる
・価格が同じデータの場合、古い日付のデータを取得

例：
・条件…日付が20040101以前

＜検索対象データ＞
ID　 日付　　　 価格
------------------------
1　　 20000101　　 100
1 　　20010201　　 100
1 　　20020301　　 200
2 　　20020401　　 300
2 　　20000501　 400
2 　　20010601　　 400
2 　　20030701　　 300
3 　　20000801　　 500
3 　　20010901　 600
3 　　20040901　 600

＜取得したい結果＞
ID　 日付　　　 価格
------------------------
1 　　20020301　　 200
1　　 20000101　　 100
2 　　20020401　　 300
2 　　20000501　 400
3 　　20010901　 600
3 　　20000801　　 500

なるべく少ないSelect文で取得したいと考えております。
よろしくお願いします。

No.2 回答者： nora1962 回答日時： 2012/09/14 11:20

with T as ( select t1.*

from ( select 検索対象データ.*,
dense_rank() over( partition by ID order by 価格 ) rnk
from 検索対象データ ) t1
where t1.rnk <= 2 )
select T.ID, T.日付, T.価格 from T
where not exists
( select 1 from T T2
where T.ID=T2.ID
and T.価格=T2.価格
and T.日付>T2.日付 )
order by T.ID, T.日付 desc

No.1 回答者： yamada_g 回答日時： 2012/09/14 10:40

SQL Serverの環境がないので動作確認できていませんが。

with 共通テーブル as(
　select id,日付,価格,row_number() over(partition by id order by 日付 desc) rn
　from テーブル
)
select id,日付,価格
from (
　select id,日付,価格,価格変動,count(価格変動) over(partition by id order by 日付 desc) 変動回数
　from (
　　select id,日付,価格
　　　　　,case when
　　　　　　　coalesce(
　　　　　　　　(select 価格 from 共通テーブル tmp
　　　　　　　　where tmp.id = t1.id
　　　　　　　　and tmp.rn = t1.rn + 1)
　　　　　　　,価格+1) != 価格
　　　　　　then 1
　　　　　　else null
　　　　　end 価格変動
　　from 共通テーブル t1
　) t2
)
where 変動回数 <= 2
and 価格変動 = 1
order by id,日付 desc;

全角スペースでインデントしているので、実行時には置き換えるか削除してください。