Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get the earliest date for a customer order

PugzlyJul 31 2022

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;
This post has been answered by Solomon Yakobson on Jul 31 2022
Jump to Answer
Comments
Post Details
Added on Jul 31 2022
4 comments
416 views