finding a max record

[JazzMaan]

I am trying to write a query to fetch
all orders, and its their latest event

o.* all orders, e.event_description
where their latest event from order_event

Tables:
orders 1 record for every order
order_events many events for every order
event_types description for a event


select o.order_id, e.event_description
from orders o, order_events oe, event_types e
where o.order_id = oe.order_id
and oe.event_id = e.event_id

How do only get the last event from order_event max (oe.create_date) ?

Thanks

 

[JazzMaan]

I tried using something like:
select * from order_events d where d.order_id = 1 and
create_date =
(select max(create_date)
from order_events e where e.order_id = 1)

but there are multiple records in order_events with same date, i just want the last one.

example dates:
record 1 4/4/2006 3:37:34 PM
record 2 4/4/2006 3:37:34 PM

 

[Dagon]

You could add a further condition:

select * from order_events d where d.order_id = 1 and
create_date =
(select max(create_date)
from order_events e where e.order_id = d.order_id)
and rowid =
(select max(rowid)
from order_events e2 where e2.order_id = d.order_id
and e2.create_date = d.create_date)

Or you could use an analytic function

select * from
(select e.*, row_number() over (partition by order_id order by create_date desc) as rn
from order_events)
where rn=1