Use rank command to limit find last purchase-CodePudding

I'm trying to find the last purchase for each customer_id. Since there are 3 customers I was expecting to get back 3 rows but I'm getting more.

Can someone tell me what's wrong and how to fix this issue. Any help would be greatly appreciated


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME) AS
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3,102, 4,TIMESTAMP '2022-10-10 17:00:00'   NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

with cte as
     (select 
        CUSTOMER_ID, 
        PRODUCT_ID, 
        QUANTITY, 
        PURCHASE_DATE,
        rank() over (partition by customer_id order by purchase_date desc) rnk
      from purchases
     )
       SELECT p.customer_id,
                c.first_name,
                c.last_name,
                p.product_id,
                i.product_name,
                p.quantity, 
                p.purchase_date   
    from cte p   
 JOIN customers c ON c.customer_id = p.customer_id
                   JOIN items i ON i.product_id = p.product_id 
   where rnk = 1:

CodePudding user response：

First, don't use RANK or DENSE_RANK - they will assign identical purchase_date values with the same rank and hence give you more than one "1" value. Use ROW_NUMBER instead.

Second, you have "from cte p" in there twice. Remove the second one.

And lastly, the real answer to your question is that you have a semicolon before the "where rank = 1" and so nothing after the semicolon is being executed. Hence it isn't filtering. A semicolon ends the SQL, completely.