title: ROW_NUMBER() over (Partition by....) to return specific row
body: <p>My query looks like this:</p>
  
  <pre><code>with T1 as (
  Select
  Dept_No,
  Product_No,
  Order_No,
  Order_Type
  Row_number() over (partition by Product_ID order by Order_No desc) as "COUNT"
  
  From Orders_Table)
  
  Select * from T1
  where ("COUNT"  = '1' and "Order_Type" &lt;&gt; 'Cancel')
  or ("COUNT" = '2' AND "Order_Type" &lt;&gt; 'Cancel'
  </code></pre>
  
  <p>So I'm trying to pull the most recent order that was not canceled.  Essentially my ROW_number() over (partition by...) function labels the orders in sequential order with 1 being the most recent order and 2 being the second most recent order.  The issue is that with this query it pulls both the most recent, and 2nd most recent order.  I am trying to write this to where if it only gives one or the other.  So if COUNT = 1 and the order_type is not cancel, only show me that record.  If not then show me the 2nd. </p>
  
  <p>Thanks so much for the help in advance.  This is being done in Toad for Oracle 9.5 using SQL tab.</p>