How to fetch last row order by date and group by id I am trying to skip duplicate history_id and take only last update row

SELECT products.products_id, products.products_date, products.products_link,
       history_products.history_products_name, history_products.history_products_update, 
       history_products.history_products_stock, history_products.history_products_status

FROM products JOIN history_products
                ON products.products_id=history_products.history_products_id

WHERE products.products_author_id=:products_author_id
   &amp;&amp; products.products_store_id=:products_store_id

GROUP BY history_products.history_products_id

ORDER BY STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") DESC
If I'm following you correctly, for each row in products, you want the most recent matching row in history_products. This query should do that:

SELECT prod.products_id, prod.products_date, prod.products_link
       history_products.history_products_name, history_products.history_products_update, 
       history_products.history_products_stock, history_products.history_products_status
  FROM history_products
         JOIN (
               SELECT products.products_id, products.products_date, products.products_link,
                      MAX(STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p")) as update_date
                 FROM products JOIN history_products
                                 ON products.products_id=history_products.history_products_id
                WHERE products.products_author_id=:products_author_id
                   &amp;&amp; products.products_store_id=:products_store_id
                GROUP BY products.products_id, products.products_date, products.products_link
              ) prod ON (    history_products.history_products_id = prod.products_id
                         AND STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") = prod.update_date
                        )
;


Here's how it works:


In the subquery referred to as prod, we select the necessary columns from the products table, plus the maximum update date for that product.
Then, we connect back to history_products again, this time to pick up the details of the history_products row for the current products_id value, for the row where the history_products_update value matches the most recent update date.
NOTE: this query does assume that, for any given row in products, there will be one and only one history_products row with a given history_products_update date. If there is more than one history record for a specified date, then additional information would need to be available to determine which history_products record was actually the last one.


Since the ORDER BY in your original question appeared to have more to do with selecting the proper history_products row than with sorting the results, I have not included a sort order for the results. If the results should show the most recently updated product first, through the least recently updated product, you can add the following ORDER BY before the closing semi-colon:

ORDER BY prod.update_date DESC


NOTE: code not tested.