sqlserverで同一キー単位で金額が最大値以外を抽出したい

お世話になります。
件名について、ご教授ください。
以下テーブルで同一キー単位で金額が最大値以外を抽出したいです。
申し訳ありませんがご教授ください。

【テーブル】
No キー 金額
1 001 10,000
2 001　 20,000
3 002 　50,000

【実行結果】※イメージ
No キー 金額
2 001　 20,000
3 002 　50,000

質問者からの補足コメント

• 最大値以外なので以下の誤りでした。
  失礼いたしました。
  
  【実行結果】※イメージ
  No キー 金額
  2 001　 10,000
  3 002 　50,000
  
  ＞＞よろずやkinchanさま
  ご指摘ありがとうございます。

    補足日時：2018/08/29 08:44

• 

  補足させていただきます。
  キー単位の最大値以外すべてを抽出するイメージになります。
  言葉足らずで申し訳ありません。
  
  【テーブル】
  No キー 金額
  1 001 10,000
  2 001　 20,000
  3 001 　30,000
  4 002 　50,000
  
  【実行結果】※イメージ
  No キー 金額
  1 001 10,000
  2 001　 20,000
  4 002 　50,000

    補足日時：2018/08/29 12:41

No.1 回答者： よろずやkinchan 回答日時： 2018/08/28 22:22

【実行結果】※イメージ

No キー 金額
2 001　 20,000
3 002 　50,000

この実行結果だと、同一キー単位で金額が最大値のものですよね。

No.2 回答者： naktak 回答日時： 2018/08/29 11:09

金額が数値項目であること、キーごとに金額が同一のレコードが複数ないことを前提としたら。

SELECT
A.No
, A.キー
, A.金額
FROM
テーブル A
WHERE
NOT EXISTS (
SELECT
1
FROM
テーブル B
WHERE
B.キー = A.キー
AND B.金額 < A.金額
)

この回答へのお礼

回答ありがとうございます。
前提としてはそれで問題ありません。
最大値以外すべての値を抽出したいです。（言葉足らずですみません。。）
※イメージは補足に記載させていただきました。

お礼日時：2018/08/29 12:31

No.3 回答者： naktak 回答日時： 2018/08/29 13:11

SELECT

A.No
, A.キー
, A.金額
FROM
テーブル A
WHERE
A.金額 < (SELECT CASE COUNT(B.No) WHEN 1 THEN 99999 ELSE MAX(B.金額) END FROM テーブル B WHERE B.キー = A.キー)

99999は項目の許容桁数の最大値

でどうですかね。

この回答へのお礼

回答いただきありがとうございます。
見直したところ 'No' は使用できないこと判明しました。
なので提示いただいた文で'No'を'金額'で代替したところ期待した結果になりました。
懸念点などありましたらお手数ですがご教授ください。

SELECT A.キー,
A.金額
FROM テーブル A
WHERE A.金額 < (
SELECT
CASE COUNT(B.金額)
WHEN 1
THEN 99999999999999999999
ELSE
MAX(B.金額)
END
FROM テーブル B
WHERE B.キー = A.キー)

お礼日時：2018/08/29 14:22

No.4 ベストアンサー 回答者： naktak 回答日時： 2018/08/29 14:50

B.Noが通りませんでしたか？

SQL Serverに実際にテーブルおよびレコードを投入し、動作するSQLを貼り付けたのですが。

まぁ動けばいいですが、金額よりはキーにしたほうがいいでしょうね。
金額はインデックス情報に最も程遠い列項目のはずなので。

この回答へのお礼

そもそもNoという列がテーブルに存在しませんでした。
金額しかキーがないデータも存在するので致し方ないと思います。
回答ありがとうございます。

お礼日時：2018/08/29 16:11