Home / Questions / 322005
Accepted
Sergi
Asked: 2020-01-16 08:19:10 +0800 CST 2020-01-16 08:19:10 +0800 CST

How to select the product with the most recent date ONLY if it has been purchased more than once?

  • 772

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

1 Answers

  1. Best Answer

    Following what you say in your last comment: that you are looking for the last transaction according FECHAto each client, product, I tell you that one way to solve it would be:

    SELECT *
    FROM VENTAS V
    INNER JOIN (SELECT CATEGORIA, 
            ID_CLIENTE, 
            PRODUCTO, 
            MAX(FECHA) FECHA
            FROM VENTAS
            GROUP BY CATEGORIA, ID_CLIENTE, PRODUCTO
    ) M
    ON V.ID_CLIENTE = M.ID_CLIENTE
    AND V.CATEGORIA = M.CATEGORIA
    AND V.FECHA = M.FECHA

    Basically your subquery brings us the last date of each CATEGORIA, ID_CLIENTEand PRODUCTO, the product category is optional if each product has only one category. We then leverage this data in a INNER JOINto get the row VENTASthat matches as far as the latest date.

    Note : This succeeds in retrieving one row per group, as long as there is no more than one row per date per group.

    Another slightly less standard way is to use row_number()to number by group and according to descending date order, and then keep the first case of each group.

    WITH CTE AS (
    SELECT  *,
        ROW_NUMBER() OVER (OVER PARTITION BY  CATEGORIA, ID_CLIENTE, PRODUCTO ORDER BY FECHA DESC) AS RN
        FROM VENTAS
)
SELECT  *
    FROM CTE
    WHERE RN = 1
    • 4