CODEWITHSUNDEEP
sqloracle-database
Sergio Pantano
Sergio Pantano

Reputation: 141

Last order item in Oracle SQL

I need to list columns from customer table, the date from first order and all data from last one, in a 1:N relationship between customer and order tables. I'm using Oracle 10g.

How the best way to do that?

TABLE CUSTOMER
---------------
id              NUMBER
name            VARCHAR2(200)
subscribe_date  DATE


TABLE ORDER
---------------
id              NUMBER
id_order        NUMBER
purchase_date   DATE
purchase_value  NUMBER

Upvotes: 0

Views: 403

Answers (4)

sgeddes
sgeddes

Reputation: 62851

Maybe something like this assuming the ID field in the Order table is actually the Customer ID:

SELECT C.*, O1.*, O2.purchase_Date as FirstPurchaseDate
FROM Customer C
LEFT JOIN 
(
  SELECT Max(purchase_date) as pdate, id
  FROM Orders
  GROUP BY id
) MaxPurchaseOrder 
  ON C.Id = MaxPurchaseOrder.Id
LEFT JOIN Orders O1 
  ON MaxPurchaseOrder.pdate = O1.purchase_date 
  AND MaxPurchaseOrder.id = O1.id
LEFT JOIN 
(
  SELECT Min(purchase_date) as pdate, id
  FROM Orders
  GROUP BY id
) MinPurchaseOrder 
  ON C.Id = MinPurchaseOrder.Id
LEFT JOIN Orders O2 
  ON MinPurchaseOrder.pdate = O2.purchase_date 
  AND MinPurchaseOrder.id = O2.id

And the sql fiddle.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

It's not obvious how to join the customer table to the orders table (order is a reserved word in Oracle so your table can't be named order). If we assume that the id_order in orders joins to the id in customer

SELECT c.id customer_id,
       c.name name,
       c.subscribe_date,
       o.first_purchase_date,
       o.id last_order_id,
       o.purchase_date last_order_purchase_date,
       o.purchase_value last_order_purchase_value
  FROM customer c
       JOIN (SELECT o.*,
                    min(o.purchase_date) over (partition by id_order) first_purchase_date,
                    rank() over (partition by id_order order by purchase_date desc) rnk
               FROM orders o) o ON (c.id = o.id_order)
 WHERE rnk = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Here is one way of doing it, using the row_number function, one join, and on aggregation:

select c.*,
       min(o.purchase_date) as FirstPurchaseDate, 
       min(case when seqnum = 1 then o.id_order end) as Last_IdOrder,
       min(case when seqnum = 1 then o.purchase_date end) as Last_PurchaseDate,
       min(case when seqnum = 1 then o.purchase_value end) as Last_PurchaseValue
from Customer c join
     (select o.*,
             row_number() over (partition by o.id order by purchase_date desc) as seqnum
      from orders o
     ) o
     on c.customer_id = o.order_id
group by c.customer_id, c.name, c.subscribe_date

Upvotes: 1

Marlin Pierce
Marlin Pierce

Reputation: 10089

I'm confused by your field names, but I'm going to assume that ORDER.id is the id in the CUSTOMER table.

The earliest order date is easy.

select CUSTOMER.*, min(ORDER.purchase_date)
from CUSTOMER
  inner join ORDER on CUSTOMER.id = ORDER.id
group by CUSTOMER.*

To get the last order data, join this to the ORDER table again.

select CUSTOMER.*, min(ORD_FIRST.purchase_date), ORD_LAST.*
from CUSTOMER
  inner join ORDER ORD_FIRST on CUSTOMER.id = ORD_FIRST.id
  inner join ORDER ORD_LAST on CUSTOMER.id = ORD_LAST.id
group by CUSTOMER.*, ORD_LAST.*
having ORD_LAST.purchase_date = max(ORD_FIRST.purchase_date)

Upvotes: 0

Related Questions