The idea is a little different from the previous query whereby here, we'll focus on generating the ROW_NUMBER() based on specific conditions, then make that as a sub-query and only add a WHERE. This can only run on MySQL v8+ or MariaDB 10.2+ (and above) that support window function. ROW_NUMBER() OVER (PARTITION BY idproduct ORDER BY exp_date) rn The idea is to get the smallest exp_date group by idproduct, then make it as a sub-query, join it with product table again to match those two extracted value so that we can extract the selling_price. You should be able to run the query above in any version of MySQL or MariaDB. The first one is this: SELECT p1.idproduct, Next is, I'm going to give two queries that you can use depending on your MySQL version. It is made so that you can do database functions through GUI. MySQL is a database and Navicat is a tool - equivalent to other tools like MySQL Workbench, PHPMyAdmin or SQLyog. Sometimes's my query has some error, anyway I need help to resolve this problem,įirst of all, let me correct you that is not a Navicat query, that's a MySQL query.

PRODUCT_EXP_DATE TABLE idproduct_exp_date idproduct exp_date selling_price Product.idproduct = product_exp_date.idproductīut it's given me different result idproduct exp_date selling_priceīut I need to get this result idproduct exp_date selling_price

I need to filter the data with the nearest exp-date and selling-price, without repeating id_product, I try to resolve this problem, but I can't get a proper way to doit

Navicat for mysql v8