Home / user-155193

Sergi's questions

Martin Hope
Sergi
Asked: 2020-01-16 08:19:10 +0800 CST
  • 3

It is difficult for me to write a query that based on the table that I show below, I selected the most recent date for the repeated products.

categoria    / Id_liente  / Producto  / Fecha
Limpieza    /  1          /  Lejia    / 20-02-2018
Limpieza    /  1          / Lejia     / 31-12-2019
Alimentacion/  1          / Vino      / 20-12-2019

After executing the query it would be:

categoria    / Id_liente  / Producto  / Fecha
Limpieza    /  1          / Lejia     / 31-12-2019
Alimentacion/  1          / Vino      / 20-12-2019

In ORACLE I have used Max(Fecha)and GROUP BYbut it returns (logically) the last transaction made for each client.

SELECT *
FROM (
    SELECT CATEGORIA,ID_CLIENTE, PRODUCTO, MAX(FECHA) FECHA
                            FROM VENTAS
                            GROUP BY CATEGORIA, ID_CLIENTE, PRODUCTO
)

(With code similar to what I've seen in SQL Query grouping and with latest date )

I'm having a hard time seeing how I can make SQL only apply MAX(Fecha)for each type of product and customer.

Could someone guide me to perform the query in ORACLE? a greeting.

oracle