Filter multiple id values ​​using Navicat in MySQL database

Question

I need to filter the data using the most recent expiry date and sale price without repeating the id_product, I tried to solve this problem but I can't find the right way to do it

This is the Navicat query and results

SELECT
    product_exp_date.idproduct,
    product_exp_date.exp_date,
    product_exp_date.selling_price
FROM
    product
    INNER JOIN
    product_exp_date
    ON
        product.idproduct = product_exp_date.idproduct
GROUP BY
    product_exp_date.exp_date

idproduct exp_date selling_price
8 2022-11-01 300
5 2022-06-08 370
5 2022-06-09 350
7 2022-07-01 380
5 2022-09-20 450
6 2022-10-08 140
6 2023-06-08 150

I've tried this way

GROUP BY
    product_exp_date.idproduct

But it gives me different results

idproduct exp_date selling_price
5 2022-06-09 350
6 2023-06-08 150
7 2022-07-01 380
8 2022-11-01 300

But I need to get this result

idproduct exp_date selling_price
5 2022-06-08 370
6 2022-10-08 140
7 2022-07-01 380
8 2022-11-01 300

Product List

productid product_name
5A
6B
7 C
8 D

Product_EXP_DateTable

idproduct_exp_date idproduct exp_date selling_price
      1 5 2022-06-09 350
      2 6 2023-06-08 150
      3 5 2022-06-08 370
      4 5 2022-09-20 450
      5 6 2022-10-08 140
      6 7 2022-07-01 380
      7 8 2022-11-01 300

Sometimes my query has some errors, anyway I need help to solve this problem, Thanks.

Answer 1

First, let me correct you; that's not a Navicat query, that's a MySQL query. Now, these are two different things. MySQL is a database, Navicat is a tool - similar to other tools like MySQL Workbench, PHPMyAdmin or SQLyog. It is designed to allow you to perform database functions through a graphical interface.

Next, I will give two queries, you can use one of them depending on your MySQL version. The first query is as follows:

SELECT p1.idproduct,
       p1.exp_date,
       p1.selling_price
FROM product_exp_date p1 
JOIN (
   SELECT idproduct,
       MIN(exp_date) AS minexp
   FROM product_exp_date
   GROUP BY idproduct
  ) AS p2
 ON p1.idproduct=p2.idproduct 
 AND p1.exp_date=p2.minexp
ORDER BY p1.idproduct;

You should be able to run the above query in any version of MySQL or MariaDB. The idea of ​​this query is to obtain the smallest exp_date by idproduct grouping, and then use it as a subquery to connect with the product table again to match these two Extracted value so that we can extract selling_price.

Second query:

SELECT idproduct,
       exp_date,
       selling_price
FROM (
   SELECT idproduct,
          exp_date,
          selling_price,
          ROW_NUMBER() OVER (PARTITION BY idproduct ORDER BY exp_date) rn
   FROM product_exp_date
  ) AS p1
WHERE rn=1;

This query can only be run on MySQL v8 or MariaDB 10.2 (and above) that supports window functions. The idea is a bit different compared to the previous query, here we will focus on generating ROW_NUMBER() based on specific conditions, then make it a subquery and only add a WHERE clause . Compared to the previous query, this requires no JOIN operation.

You can see that I didn't take into account the product table because I didn't see it being used in your original query, but if you do need to join it and you can't get Know how to do it, just drop me a message and I'll see what I can do.

Demo fiddle