Forum Stats

  • 3,838,263 Users
  • 2,262,349 Discussions
  • 7,900,568 Comments

Discussions

Get the earliest date for a customer order

User_G3NSO
User_G3NSO Member Posts: 44 Green Ribbon

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;
Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 Red Diamond
    Answer ✓
    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.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond

    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
    Solomon Yakobson Member Posts: 19,586 Red Diamond
    Answer ✓
    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.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,586 Red Diamond

    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.