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

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

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です。
よろしくお願いいたします。

m.ts10806

2021/01/02 18:29

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

mecab

2021/01/02 18:41

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

m.ts10806

2021/01/02 18:54

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

行動規範の内容に同意します

回答2件

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

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

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

sql
1select
2    mxpriceitem.item
3    ,max_price
4    ,mxpriceitem.date max_date
5from
6    (
7    SELECT
8        max(id) id,
9        max(price) max_price
10    FROM
11        item 
12    GROUP BY
13        item
14) mxitem
15 join item mxpriceitem ON
16    mxitem.id = mxpriceitem.id

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

SQL
1SELECT
2    max_item.item,
3    max_price,
4    max_date,
5    min_price,
6    min_date
7FROM
8    (
9    SELECT
10        mxpriceitem.item,
11        max_price,
12        mxpriceitem.date max_date
13    FROM
14        (
15        SELECT
16            MAX(id) id,
17            MAX(price) max_price
18        FROM
19            item
20        GROUP BY
21            item
22    ) mxitem
23JOIN item mxpriceitem ON
24    mxitem.id = mxpriceitem.id
25) max_item,
26(
27    SELECT
28        mnpriceitem.item,
29        min_price,
30        mnpriceitem.date min_date
31    FROM
32        (
33        SELECT
34            MIN(id) id,
35            MIN(price) min_price
36        FROM
37            item
38        GROUP BY
39            item
40    ) mnitem
41JOIN item mnpriceitem ON
42    mnitem.id = mnpriceitem.id
43) min_item
44WHERE
45    max_item.item = min_item.item