ROW_NUMBER() over (Partition by....) to return specific row

Question

My query looks like this:

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" <> 'Cancel')
or ("COUNT" = '2' AND "Order_Type" <> 'Cancel'

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.

This is being done in Toad for Oracle 9.5 using SQL tab.

Add the "Order_Type" <> 'Cancel' to the cte, and then only look for "Count" = '1' in the second query. Try that. — websch01ar, Sep 29 '14 at 14:25
Websch01ar. I just was thinking that right before I read your comment. Great idea! Let me check and then mark as answer — user3486773, Sep 29 '14 at 14:46

score 1 · Answer 1 · answered Sep 29 '14 at 14:55

But you have an or.
If both or are satisfied you will get two rows.

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
where "Order_Type" <> 'Cancel')

Select * from T1
where "COUNT"  = '1'

score 0 · Answer 2 · answered Nov 20 '17 at 21:19

Use a case expression to control which rows get a result from row_number(), here we avoid numbering any rows that have been cancelled:

WITH t1 AS (
      SELECT
            Dept_No
          , Product_No
          , Order_No
          , Order_Type
            /*  the most recent order that was not canceled */
          , case when order_type <> 'Cancel' 
                 then ROW_NUMBER() OVER (PARTITION BY Product_ID 
                                          ORDER BY Order_No DESC)
                 end AS is_recent
       FROM Orders_Table
      )
SELECT
      *
FROM t1
WHERE is_recent = 1

Or, perhaps the easiest way is to simply exclude cancelled orders e.g.

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 is_recent
       FROM Orders_Table
       WHERE order_type <> 'Cancel' 
      )
SELECT
      *
FROM t1
WHERE is_recent = 1

nb: row_number() returns an integer, so don't compare that column to a string

ROW_NUMBER() over (Partition by....) to return specific row

2 Answers2