Take the 2-minute tour ×

I have below data:


order_id    order_name        order_date         order_status 

1            iphone        20130102 13:20:00        cancelled
1            blackberry    20130102 13:00:00        cancelled
1            ipad          20130102 13:00:00        cancelled


person_id     person_name       order_id

1             harshini          1

I want to retrieve the below data when i query based on order_date between 20130102 13:00:00 to 2013 13:20:00.It means last cancel order.

person_name     order_name   order_date     

harshini        blackberry   20130102 13:00:00
share|improve this question
They all have the same order ID same order date, on what basis did you select blackberry and not ipad for e.g. ? –  Techmonk Mar 4 '13 at 12:52
Not an answer, but are you sure about your data model? In that model, a person can have only one order, while an order can have several people associated with it...? –  Joachim Isaksson Mar 4 '13 at 12:53
@Joachim: Person can order multiple products. –  user2127414 Mar 4 '13 at 20:30

2 Answers 2

select person_name, order_name, order_date 
   o.order_id, o.order_name, o.order_date, o.order_status,
   p.person_id, p.person_name, p.order_id,
   row_number() over (/*partition by person_id*/ order by order_date desc) as rnk
  from order o join person p on (o.order_id = p.order_id)
  where o.order_status = 'canceled'
   and o.order_date between 
       to_date('20130102 13:00:00','yyyymmdd hh24:mi:ss') and
where rnk = 1;

But see the comments, you have some issues in your design.

share|improve this answer
I want to retrieve record balckberry if i query between 13:00:00 to 13:20:00.it means last order record –  user2127414 Mar 4 '13 at 17:51
order Iphone is beteween your dates. –  Florin Ghita Mar 4 '13 at 17:53
actually i want to display last cancelled record i.e second one(black berry) –  user2127414 Mar 4 '13 at 19:19
@user2127414 If you can't order by anything, there is no concept of "last" in a relational database. You'll need a column that is increasing in some way you decide what's 'last', otherwise, if you order by order_date, either the cancellation of the iPad or the blackberry are equally valid answers since their order dates are the same. –  Joachim Isaksson Mar 5 '13 at 4:21

just try with this...
select p.person_name ,o.order_name,o.order_status from order_1 o,person p where orderdate=(select max(orderdate) from order_1)

share|improve this answer

Your Answer


