So, I have this query where I am trying to get the most recent sale price within several records.

CREATE TABLE `codes` (
  `code_father` longtext CHARACTER SET utf8mb4,
  `code_son` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `prices` (
  `code_son` varchar(22) CHARACTER SET utf8mb4 NOT NULL,
  `price` float,
  `date` date,
  KEY `code_son` (`code_son`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `codes`
(`code_father`,
`code_son`)
VALUES
('ABC000001','ADV000055');
('ABC000001','ADV000045');
('ABC000001','ADV000035');
('ABC000001','ADV000015');
('ABC000002','ADV000079');
('ABC000002','ADV000077');
('ABC000007','ADV000040');
('ABC000008','ADV000030');

INSERT INTO `prices`
(`code_son`,
`price`,
`date`)
VALUES
('ADV000055','29.99','2021-11-06');
('ADV000045','9.99','2021-12-04');
('ADV000035','9.99','2021-12-01');
('ADV000015','245.00','2021-12-06');
('ADV000045','1999.99','2021-11-03');
('ADV000035','29.99','2021-11-09');
('ADV000079','29.99','2021-11-21');
('ADV000077','29.99','2021-11-16');
('ADV000077','29.99','2021-12-04');
('ADV000040','29.99','2021-11-04');
('ADV000030','29.99','2021-11-26');
('ADV000030','29.99','2021-10-21');

This is the query, does not work:

SELECT c.code_father, c.code_son, p.price, p.date
FROM prices p
INNER JOIN (SELECT code_son, price, MAX(date)as date FROM prices GROUP BY code_son)as t1 USING(code_son, date)
LEFT JOIN codes c ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'

this is what should be returned

See the example here

CodePudding user response：

First of all, you have to change ; to ,, or else, only the first record will get inserted.

I guess this may be what you are looking for.

I just added a order by desc and limit.

SELECT c.code_father, c.code_son, p.price, p.date
FROM prices p
INNER JOIN (SELECT code_son, price, MAX(date) as date FROM prices GROUP BY code_son)as t1 USING(code_son, date)
LEFT JOIN codes c ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'
ORDER BY p.date DESC
LIMIT 1

CodePudding user response：

What means the most recent sale price? MAX price OR MAX date?

Search first for MAX price, if there are several MAX price, then for MAX Date

SELECT c.code_father, c.code_son, p.price, p.date
FROM prices p
INNER JOIN codes c ON p.code_son = c.code_son
ORDER BY p.price DESC, p.date DESC
LIMIT 1