MYSQLで、MAX、MINを取得した際にそれぞれ同じ行の別のカラム値を取得したいです。

Question

このようなテーブルがあります。

id|item|price|date
1|'A'|100|2020-12-10
2|'A'|150|2020-12-11
3|'A'|100|2020-12-12
4|'A'|200|2020-12-13

SELECT item, MAX(price), MIN(price) FROM table GROUP BY item;

'A'|200|100|

上記に加えて、最高価格のときのdate、最低価格のときのdateを取得するにはどうすれば良いでしょうか？
価格が被った場合はdateは降順でも昇順でもよいのですが・・
itemはA以外もあります。

以下はイメージです。
SELECT item, MAX(price), date AS max_date MIN(price), date AS min_date FROM table GROUP BY item;

'A'|200|2020-12-13|100|2020-12-12

使用しているのはMYSQL5.6.43です。
よろしくお願いいたします。

Comments

[m.ts10806]

itemはAしかないのでしょうか。

[mecab]

すみません、A以外もあります。

[m.ts10806]

イメージのSQLの文法エラーがでるところは除いておいてください。
イメージとは言え、エラーで悩んでるのか想定通りの情報がでないのかで違いますし、
「なぜ提示されたSQLなのか」というところがちょっと不明なので。

Answer 1

「別々にサブクエリで取得して自己結合」のような形になるかと思います。
ただ、item,dateをとるときのコツが必要で、「maxのときのID、minのときのID」を取っておく必要があります。

テストデータあんまり作らず組んだので、あくまで例です。今回の回答をヒントとして、自身でもテストデータ増やして試してみてください。

maxだけの例　※「table」という名前は予約語なので「item」という名前に置き換えました

select
    mxpriceitem.item
    ,max_price
    ,mxpriceitem.date max_date
from
    (
    SELECT
        max(id) id,
        max(price) max_price
    FROM
        item 
    GROUP BY
        item
) mxitem
 join item mxpriceitem ON
    mxitem.id = mxpriceitem.id

これを基盤として、minも作って結合します。

SELECT
    max_item.item,
    max_price,
    max_date,
    min_price,
    min_date
FROM
    (
    SELECT
        mxpriceitem.item,
        max_price,
        mxpriceitem.date max_date
    FROM
        (
        SELECT
            MAX(id) id,
            MAX(price) max_price
        FROM
            item
        GROUP BY
            item
    ) mxitem
JOIN item mxpriceitem ON
    mxitem.id = mxpriceitem.id
) max_item,
(
    SELECT
        mnpriceitem.item,
        min_price,
        mnpriceitem.date min_date
    FROM
        (
        SELECT
            MIN(id) id,
            MIN(price) min_price
        FROM
            item
        GROUP BY
            item
    ) mnitem
JOIN item mnpriceitem ON
    mnitem.id = mnpriceitem.id
) min_item
WHERE
    max_item.item = min_item.item

可能ならVIEWを使いたいですね。
MySQL8ならwithという手があるんですが。

あとは、

価格が被った場合はdateは降順でも昇順でもよいのですが・・

これは「仕様」なので、決めたうえでご対応ください。

Comments

[mecab]

ありがとうございます。
SELECT
MAX(id) id,
MAX(price) max_price
FROM
item
GROUP BY
item
を実行したところ、max_priceに紐づくidではなく、idも最大値が返ってきてしまいます。

[m.ts10806]

回答に太字で書いてます。
>テストデータあんまり作らず組んだので、あくまで例です。今回の回答をヒントとして、自身でもテストデータ増やして試してみてください。

方針は十分伝わる内容かと思います。作業依頼をしたいのでしたらそれなりの対価を払って別のところへご依頼ください。

Answer 2

解決済み解決済み解決済み解決済み解決済み解決済み