sql query to fetch first and last records of each month for two products-CodePudding

hello guys please i'm writing a query that can give me the first and last records of each month which is done but the case is i'm retrieving data for two products using the code below

SELECT u1.product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT month, MIN(date) mindt, MAX(date) maxdt 
       FROM closingstock 
       where product='PMS' 
       GROUP BY month) v 
JOIN closingstock u1 ON u1.date=v.mindt 
JOIN closingstock u2 ON u2.date=v.maxdt;

but the result is in the screen shot below and i want only the highlighted result please any solution for that the screenshot is below enter image description here

CodePudding user response：

SELECT product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT product, `month`, MIN(`date`) mindt, MAX(`date`) maxdt 
       FROM closingstock 
       WHERE product IN ('Product 1', 'Product 2')  -- the list of needed products
       GROUP BY product, `month`) v 
JOIN closingstock u1 USING (product)
JOIN closingstock u2 USING (product)
WHERE u1.`date`=v.mindt
  AND u2.`date`=v.maxdt;