表を結合し、日付が新しい順に並べたい

Question

やりたこと

「表を結合し、日付が新しい順に並べたい」です

赤丸の3行を取得したいです。

問題点

以下のクエリを実行すると2018-11-13 00:00:00のデータが3件取得されてしまいます。

select
	*
from item_master im
inner join item_prices ip on im.id = ip.item_id
group by
	ip.item_id
order by
	ip.created_at desc,
	ip.item_id asc

確認のため以下の通り group by を外したクエリを実行すると問題なくソートされているのですがなぜ最新の15日のデータが取れず、13日のデータになってしまうのでしょうか？
また、15日の最新のデータを取るにはどのようなSQLを書けばよいでしょうか？

select
	*
from item_master im
inner join item_prices ip on im.id = ip.item_id
/* group by
	ip.item_id */
order by
	ip.created_at desc,
	ip.item_id asc

データベース テーブル構成

CREATE TABLE IF NOT EXISTS `item_master` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `item_master` (`id`, `name`) VALUES
	(1, 'りんご'),
	(2, 'みかん'),
	(3, 'バナナ');

CREATE TABLE IF NOT EXISTS `item_prices` (
  `item_id` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `item_prices` (`item_id`, `price`, `created_at`) VALUES
	(1, 100, '2018-11-13 00:00:00'),
	(1, 110, '2018-11-14 00:00:00'),
	(1, 120, '2018-11-15 00:00:00'),
	(2, 130, '2018-11-13 00:00:00'),
	(2, 140, '2018-11-14 00:00:00'),
	(2, 150, '2018-11-15 00:00:00'),
	(3, 160, '2018-11-13 00:00:00'),
	(3, 170, '2018-11-14 00:00:00'),
	(3, 180, '2018-11-15 00:00:00');

MariaDB Version

10.2.12-MariaDB

Answer 1

select * from item_master as t1
inner join (
select * from item_prices as t2
where not exists(
select 1 from item_prices 
where t2.item_id=item_id and t2.created_at<created_at)
) as t3 on t1.id=t3.item_id

select *は無駄でしたね

select * from item_master as t1
inner join item_prices as t2
on t1.id=t2.item_id
and not exists(
select 1 from item_prices 
where t2.item_id=item_id and t2.created_at<created_at)

Comments

[退会済みユーザー]

ありがとうございました！

Answer 2

なぜ最新の15日のデータが取れず、13日のデータになってしまうのでしょうか？

group byを正しく使用していないからです。
group byで指定しているのが、item_idだけなので、それ以外の項目は適当にピックアップされたものです。
そもそも、昇順に並べてgroup by すると先頭のみになるなんてことはありません。

以下（yambejpさんと被らないように）相関問い合わせでのSQL

select item_id
     ,(select `name` from item_master where id=t1.item_id) as `name`  
     , price, created_at
from item_prices t1
where created_at=(
         select max(created_at) from item_prices where item_id=t1.item_id
      )

Comments

[退会済みユーザー]

ありがとうございました！