Using MIN value after JOIN MAX(date) MYSQL

Question

I have 3 table. manufacturers, products and prices

I want to get the last price of product and select min price of them.

Table manufacturers:

# manufacturers
id        name
 1        Manufacturer 1
 2        Manufacturer 2

Table products:

# products
id        name
 1        Product 1
 2        Product 2

Table prices:

# prices
id        price        manufacturerId        createdAt    
 1           10                     1        '2019-09-09 00:00:00'
 2           20                     1        '2019-09-10 00:00:00'
 3           11                     2        '2019-09-09 00:00:00'
 4           21                     2        '2019-09-10 00:00:00'

Full code:

DROP DATABASE if exists ssg ;
CREATE DATABASE ssg;
USE ssg;

# Create database manufacturers
CREATE TABLE manufacturers (id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                            name VARCHAR(256) NOT NULL);
# Insert value
INSERT INTO manufacturers (name) VALUES ('Manufacturer 1');
INSERT INTO manufacturers (name) VALUES ('Manufacturer 2');


# Create database products
CREATE TABLE products (id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                       name VARCHAR(256) NOT NULL);
# Insert value
INSERT INTO products (name) VALUES ('Product 1');

# Create database prices
CREATE TABLE prices (id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                     productId INT(11) UNSIGNED NOT NULL,
                     price BIGINT UNSIGNED NOT NULL,
                     manufacturerId INT(11) UNSIGNED NOT NULL,
                     createdAt DATETIME NOT NULL);
# Insert value
INSERT INTO prices (productId, price, manufacturerId, createdAt) VALUES (1, 10, 1, '2019-09-09 00:00:00');
INSERT INTO prices (productId, price, manufacturerId, createdAt) VALUES (1, 20, 1, '2019-09-10 00:00:00');
INSERT INTO prices (productId, price, manufacturerId, createdAt)VALUES (1, 11, 2, '2019-09-09 00:00:00');
INSERT INTO prices (productId, price, manufacturerId, createdAt)VALUES (1, 21, 2, '2019-09-10 00:00:00');

# Query
SELECT products.id, products.name, lastValue.price as latestPrice, lastValue.manufacturerId
FROM products
LEFT JOIN(
        SELECT productId, COUNT(DISTINCT manufacturerId) AS total
        FROM prices
        GROUP BY prices.productId) counts ON counts.productId = products.id
        LEFT JOIN (
            SELECT prices.*
            FROM (
                    SELECT productId, MAX(createdAt) createdAt
                    FROM prices
                    GROUP BY productId) latest
                    JOIN prices ON latest.productId = prices.productId
                    AND prices.createdAt = latest.createdAt
            ) lastValue
        ON lastValue.productId = products.id

and I got:

id        name        latestPrice        manufacturerId
 1        Product 1            20                     1
 1        Product 1            21                     2

So how can I receive products with only with the MIN of latestPrice.

I have to post it in http://sqlfiddle.com/#!9/418cb7/1 . Please "Build Schema" then "Run SQL"

Sorry for my bad english.

Answer 1

In MySQL 8.0, you can do this with window functions only:

select id, name, price, manufacturerId
from (
    select 
        t.*,
        rank() over(order by price) rn2
    from (
        select
            p.id,
            p.name,
            i.price,
            i.manufacturerId,
            rank() over(partition by p.id order by i.createdAt desc) rn1
        from products p
        inner join prices i on i.productId = p.id
    ) t
    where rn1 = 1
) t
where rn2 = 1

This phrases as:

• first rank the prices of each product by descending date, and filter on the latest price per product
• then rank the all the latest prices by ascending price, and filter on the lowest of them

Demo on DB Fiddle:

id | name      | price | manufacturerId
-: | :-------- | ----: | -------------:
 1 | Product 1 |    20 |              1