title: Using MIN value after JOIN MAX(date) MYSQL
body: <p>I have 3 table. <strong>manufacturers</strong>, <strong>products</strong> and <strong>prices</strong></p>
  
  <p>I want to get the last price of product and select min price of them.</p>
  
  <p>Table manufacturers:</p>
  
  <pre><code># manufacturers
  id        name
   1        Manufacturer 1
   2        Manufacturer 2
  </code></pre>
  
  <p>Table products:</p>
  
  <pre><code># products
  id        name
   1        Product 1
   2        Product 2
  </code></pre>
  
  <p>Table prices:</p>
  
  <pre><code># 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'
  </code></pre>
  
  <p>Full code:</p>
  
  <pre><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
  </code></pre>
  
  <p>and I got:</p>
  
  <pre><code>id        name        latestPrice        manufacturerId
   1        Product 1            20                     1
   1        Product 1            21                     2
  </code></pre>
  
  <p>So how can I receive products with only with the MIN of latestPrice.</p>
  
  <p>I have to post it in <a href="http://sqlfiddle.com/#!9/418cb7/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/418cb7/1</a> . Please "Build Schema" then "Run SQL"</p>
  
  <p>Sorry for my bad english.</p>