SQL文の書き方について

質問

SQLServer2005で、次のような品物の単価の履歴を表すテーブルを作りました。「,」は列の区切りです。
ID,Sort,Name,Price,Effective,Time
1,1,りんご,100,True,2009/4/1
2,3,なし,120,True,2009/4/1
3,2,ぶどう,150,True,2009/4/1
4,4,ばなな,80,True,2009/4/1
5,1,りんご,110,True,2009/4/20
6,3,なし,100,True,2009/4/20
7,3,なし,90,True,2009/4/25
8,4,ばなな,False,2009/4/25
このテーブルから、Effectiveが、Trueで、最新の単価をSort順に抽出するSQL文が判りません。
SELECT　*　FORM　テーブル名　WHERE　(Effective=1) ORDER BY Sort
に、何を加えれば実現可能でしょうか？
どなたか？詳しい方がいらっしゃいましたら、教えてください。
SQL文の初心者なので、簡単なことなんでしょうが、よろしくお願いいたします。入門書を読めば読むほど、こんがらかって、困っております。なお、Sortの部分は、正規表現的に問題があるので、見直そうと考えています。

回答No.3

jamshid6 · ベストアンサー

普通のSELECT文というのは、１行１行に対しての判断しかできません。
今回のように「最新の」という条件をつけたいときは、１行だけ見てもそれが最新かどうかはわからないので、他のレコードもチェックする必要があります。

したがって、結合、相関サブクエリ、分析関数のいずれかをつかって、「他の同じSort番号のものと比べて最新なのか」をチェックしなければなりません。
適用日を持って管理するテーブルの宿命ですので、どれかを理解するしかなさそうですね。
ちなみに、最も一般的なのは相関サブクエリです。

--結合で処理する例
--Sort毎の最新の日付を調べたものと内部結合することにより最新のものを表示させます

SELECT
t1.ID,t1.Sort,t1.Name,t1.Price,t1.Effective,t1.Time
FROM テーブル名 t1
INNER JOIN
(SELECT Sort,MAX(Time) MaxTime
FROM テーブル名
WHERE Effective=1
GROUP BY Sort) t2 ON t2.Sort=t1.Sort AND t2.MaxTime=t1.Time
WHERE t1.Effective=1
ORDER BY Sort

--相関サブクエリで処理する例
--それぞれの行について「日付がそのSortで最新か」を調べ、該当すれば表示させる
--外側のテーブルの情報をWHERE条件に書いたサブクエリの中で参照しているので相関サブクエリといいます。
--EXISTSを使ったバリエーションもあります。

SELECT
ID,Sort,Name,Price,Effective,Time
FROM テーブル名 t1
WHERE t1.Effective=1
AND t1.Time IN
(SELECT MAX(t2.Time) FROM テーブル名 t2
WHERE t2.Sort=t1.Sort and t2.Effective=1)
ORDER BY Sort

--分析関数で処理する例
--SQL Server 2005であればROW_NUMBER()という並べ替え番号を取得する分析関数が使えます。
--各Sort別に、日付の降順で連番を振って、１番だけを取得します

SELECT
ID,Sort,Name,Price,Effective,Time
FROM
(SELECT
ROW_NUMBER() OVER (PARTITION BY Sort ORDER BY Time DESC) Seq,
*
FROM テーブル名
WHERE Effective=1) t
WHERE Seq=1
ORDER BY Sort

お礼

早速のお返事ありがとうございます。
ご指導の通り、相関サブクエリの処理で実現できました。
但し、まねて書いてみてできただけなので、私自身は、理解できていません。折角、３種類もご指導頂いたので、じっくり勉強して、自分のものにしていきたいと、思います。
同じ結果でも、色々な方法があるのも、びっくりしています。
本当に、ありがとうございました。今後もよろしくお願いします。

回答No.5

nora1962

ID Sort Name　　Price　Effective　Time
5　1　　りんご　110　　True　　　 2009/04/20
3　2　　ぶどう　150　　True　　　 2009/04/01
7　3　　なし　　90　　 True　　　 2009/04/25
4　4　　ばなな　80　　 True　　　 2009/04/01

が欲しいなら

select * from テーブル as t1
where effective = true
and not exists
( select 1 from テーブル as t2
where t1.name = t2.name
and t1.time < t2.time )

ですが。
はずしていたらごめんなさい。

回答No.4

noah7150

SELECT　*　FORM　テーブル名　WHERE　(Effective=1) ORDER BY [Sort], [Time] DESC

＞最新の単価をSort順
もしかして
5,1,りんご,110,True,2009/4/20
3,2,ぶどう,150,True,2009/4/1
7,3,なし,90,True,2009/4/25
8,4,ばなな,False,2009/4/25
だけにしたい？（お！８行目単価カラムが無い）
それならちょっとばかり厄介
Select *
from
inner join((select ) on ) on
各品名の最新日を求め、最新のIDを求めてとなります
長くなるから最初のでよければ

回答No.2

henep325

#1 です。
質問の解釈を間違えたみたいです。
大変失礼しました。

回答No.1

henep325

＞最新の単価をSort順に抽出
ORDER BY Sort　を
ORDER BY Time DESC , Sort ASC

ORDER BY句には複数指定できます
書いた順に優先順位は高かったはず・・