Get the earliest date for a customer order

User_G3NSO

I have the following setup and I'm trying to get the earliest order_date for each customer but my CTE is giving me a syntax error, which I can't seem to resolve. Any help would be greatly appreciated.

CREATE TABLE customers(customer_id, customer_name, join_date) AS 
SELECT  1, 'ABC', DATE '2020-05-20' FROM DUAL UNION ALL
SELECT  2, 'XYZ', DATE '2020-04-03' FROM DUAL;

CREATE TABLE products(product_id, product_name) AS
SELECT  1, 'Coca Cola'  FROM DUAL UNION ALL
SELECT  1, 'Dr. Pepper'  FROM DUAL UNION ALL
SELECT  3, 'Pepsi'  FROM DUAL;

CREATE TABLE sales(customer_id, product_id, order_date) AS  
SELECT  1, 1, DATE '2021-11-20' FROM DUAL UNION ALL
SELECT  1, 2, DATE '2021-11-21' FROM DUAL UNION ALL
SELECT  1, 3, DATE '2021-11-22' FROM DUAL UNION ALL
SELECT  2, 1, DATE '2021-12-23' FROM DUAL UNION ALL
SELECT  2, 2, DATE '2021-12-21' FROM DUAL UNION ALL
SELECT 2, 3, DATE '2021-12-22' FROM DUAL;

WITH cte AS (
    SELECT  
      s.customer_id 
     ,c.customer_name
      ,s.product_id 
      ,p.product_name
      ,s.order_date
         RANK() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) rnk
    FROM sales s
               ,customers c
               ,products p
    INNER JOIN customers c ON s.customer_id = c.customer_id 
     INNER JOIN products p ON s.product_id = p.product_id 
    )
SELECT 
      customer_id 
      ,customer_name
      ,product_id 
      ,product_name
      ,order_date
FROM cte
WHERE rnk = 1;

Solomon Yakobson

1. comma is missing before RANK
2. you join same tables twice - once using native joins and then second time using ANSI joins

All works as inteneded after fixing it:

WITH CTE AS (
             SELECT  S.CUSTOMER_ID,
                     C.CUSTOMER_NAME,
                     S.PRODUCT_ID,
                     P.PRODUCT_NAME,
                     S.ORDER_DATE,
                     RANK() OVER (PARTITION BY S.CUSTOMER_ID ORDER BY S.ORDER_DATE) RNK
               FROM  SALES S,
                     CUSTOMERS C,
                     PRODUCTS P
               WHERE S.CUSTOMER_ID = C.CUSTOMER_ID
                 AND S.PRODUCT_ID = P.PRODUCT_ID
            )
SELECT  CUSTOMER_ID,
        CUSTOMER_NAME,
        PRODUCT_ID,
        PRODUCT_NAME,
        ORDER_DATE
  FROM  CTE
  WHERE RNK = 1
/

CUSTOMER_ID CUS PRODUCT_ID PRODUCT_NA ORDER_DAT
----------- --- ---------- ---------- ---------
          1 ABC          1 Coca Cola  20-NOV-21
          1 ABC          1 Dr. Pepper 20-NOV-21
          2 XYZ          3 Pepsi      22-DEC-21

SQL>

SY.

Frank Kulash

Hi, @User_G3NSO

Thanks for posting the CREATE TABLE statements; that's very helpful! Don't forget to post the exact results you want from the given sample data.

You're missing a comma in the first SELECT clause, between the last two columns (order_date and rnk). Also, you're mixing up the two ways of doing joins. If you're using the ANSI join syntax (with the keyword JOIN between tables), then don't use the older syntax (with a comma between tables).

If you fix those errors, like this:

WITH cte AS (
  SELECT  
   s.customer_id 
   ,c.customer_name
   ,s.product_id 
   ,p.product_name
   ,s.order_date
   ,RANK() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) rnk
  FROM       sales     s
  INNER JOIN customers c ON s.customer_id = c.customer_id 
  INNER JOIN products  p ON s.product_id  = p.product_id 
  )
SELECT 
   customer_id 
   ,customer_name
   ,product_id 
   ,product_name
   ,order_date
FROM cte
WHERE rnk = 1
ORDER BY  customer_id   -- or whatever you want
;

then you'll get these results:

    CUSTOMER_ID CUS      PRODUCT_ID PRODUCT_NA ORDER_DATE
--------------- ---      ---------- ---------- -----------
              1 ABC               1 Coca Cola  20-Nov-2021
              1 ABC               1 Dr. Pepper 20-Nov-2021
              2 XYZ               3 Pepsi      22-Dec-2021

Is that what you want?

Solomon Yakobson

1. comma is missing before RANK
2. you join same tables twice - once using native joins and then second time using ANSI joins

All works as inteneded after fixing it:

WITH CTE AS (
             SELECT  S.CUSTOMER_ID,
                     C.CUSTOMER_NAME,
                     S.PRODUCT_ID,
                     P.PRODUCT_NAME,
                     S.ORDER_DATE,
                     RANK() OVER (PARTITION BY S.CUSTOMER_ID ORDER BY S.ORDER_DATE) RNK
               FROM  SALES S,
                     CUSTOMERS C,
                     PRODUCTS P
               WHERE S.CUSTOMER_ID = C.CUSTOMER_ID
                 AND S.PRODUCT_ID = P.PRODUCT_ID
            )
SELECT  CUSTOMER_ID,
        CUSTOMER_NAME,
        PRODUCT_ID,
        PRODUCT_NAME,
        ORDER_DATE
  FROM  CTE
  WHERE RNK = 1
/

CUSTOMER_ID CUS PRODUCT_ID PRODUCT_NA ORDER_DAT
----------- --- ---------- ---------- ---------
          1 ABC          1 Coca Cola  20-NOV-21
          1 ABC          1 Dr. Pepper 20-NOV-21
          2 XYZ          3 Pepsi      22-DEC-21

SQL>

SY.

User_G3NSO

@Solomon Yakobson

@Frank Kulash

Thanks

Solomon Yakobson

I just noticed you have 2 products with same id of 1. I assume it was a typo and it should be:

CREATE TABLE products(product_id, product_name) AS
SELECT  1, 'Coca Cola'  FROM DUAL UNION ALL
SELECT  2, 'Dr. Pepper'  FROM DUAL UNION ALL
SELECT  3, 'Pepsi'  FROM DUAL;

WITH CTE AS (
             SELECT  S.CUSTOMER_ID,
                     C.CUSTOMER_NAME,
                     S.PRODUCT_ID,
                     P.PRODUCT_NAME,
                     S.ORDER_DATE,
                     RANK() OVER (PARTITION BY S.CUSTOMER_ID ORDER BY S.ORDER_DATE) RNK
               FROM  SALES S,
                     CUSTOMERS C,
                     PRODUCTS P
               WHERE S.CUSTOMER_ID = C.CUSTOMER_ID
                 AND S.PRODUCT_ID = P.PRODUCT_ID
            )
SELECT  CUSTOMER_ID,
        CUSTOMER_NAME,
        PRODUCT_ID,
        PRODUCT_NAME,
        ORDER_DATE
  FROM  CTE
  WHERE RNK = 1
/

CUSTOMER_ID CUS PRODUCT_ID PRODUCT_NA ORDER_DAT
----------- --- ---------- ---------- ---------
          1 ABC          1 Coca Cola  20-NOV-21
          2 XYZ          2 Dr. Pepper 21-DEC-21

SQL>

SY.