CODEWITHSUNDEEP
sqloracle-databaseplsql
danny.lesnik
danny.lesnik

Reputation: 18639

Assistance needed with join query

I have two very simple tables in Oracle 9G:

Customer 

userid | firstName | lastName | email 
-------+-----------+----------+---------------------
  1      user1       User1      [email protected]
  2      user2       User2      [email protected]

Order

orderiD | userId | OrderType | Order_Date | Amount
--------+--------+-----------+------------+-------
1          1          0         12/12/2009    1
2          1          1         13/12/2009    2
3          1          1         14/12/2009    3
4          2          0         12/12/2009    4
5          2          1         16/12/2009    2
6          1          0         14/12/2009    5
7          2          1         17/12/2009    4
8          2          0         10/12/2010    2

I want to select all users which have orders of type 0

select *    
from Customer c 
inner join Order o on c.userid = o.userid 
where o.orderType = '0'

The result is:

orderiD | userId | OrderType | Order_Date | Amount
--------+--------+-----------+------------+--------
1          1          0         12/12/2009    1
4          2          0         12/12/2009    4
6          1          0         14/12/2009    5
8          2          0         10/12/2010    2

Now I need to modify this query to bring only last purchase date for each user id and get the result like this:

orderiD | userId | OrderType | Order_Date | Amount
--------+--------+-----------+------------+--------
6          1          0         14/12/2009    5
8          2          0         10/12/2010    2

How should I modify my query to get this result?

Upvotes: 0

Views: 87

Answers (4)

Gary Myers
Gary Myers

Reputation: 35401

No subquery required, just aggregate functions.

select c.userId, o.OrderType, 
       max(o.Order_Date),  
       max(o.orderiD) keep (dense_rank last order by order_date),  
       max(o.Amount) keep (dense_rank last order by order_date)
from Customer c inner join Order o
on c.userid = o.userid where o.orderType ='0'
group by c.userId, o.OrderType

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

SELECT * 
FROM order o
WHERE o.orderType ='0'
  AND o.order_date = 
      ( SELECT MAX(o2.order_date)
        FROM order o2
        WHERE o2.userid = o.userid
          AND o2.orderType = '0'
      )

or

SELECT o.* 
FROM order o
  JOIN
    ( SELECT userid
           , MAX(order_date) AS lastPurchaseDate
      FROM order 
      WHERE o.orderType ='0'
      GROUP BY userid
    ) AS grp
    ON grp.userid = o.userid
    AND grp.lastPurchaseDate = o.order_date

Upvotes: 3

Datajam
Datajam

Reputation: 4231

Try this:

select * 
from   customer c
,      order o
where  c.userid = o.userid 
and    o.orderType ='0'
and    o.order_date = (
    select max(o2.order_date)
    from   order o2
    where  o2.userid = o.userid
)

Upvotes: 1

tofutim
tofutim

Reputation: 23374

SELECT * 
FROM Order o
INNER JOIN 
(SELECT MAX(o.id) AS maxid
FROM Customer c 
INNER JOIN Order o ON c.userid = o.userid 
WHERE o.orderType ='0'
GROUP BY c.userid) x ON x.maxid = o.id

Upvotes: -1

Related Questions