Oracle SQL : Getting the first row with filters

Question

How do I get the first row filtering some values out? I used row_number() over(partition by Name ORDER BY Date) to get the order (See example below). But I need, the rank will start at the last occurrence of Type = B (See expected output)

SELECT Name, Age, Type, Date, 
       row_number() over(partition by Name ORDER BY Date) as Rank
FROM TableA;

For example :

Name   Age   Type   Date          Rank
Ben    12      A    2013/02/01    1 
Rod    14      A    2013/02/05    2
Zed    13      B    2013/03/09    3
Ken    12      A    2013/04/02    4 
Jed    14      B    2013/05/01    5
Mar    13      A    2013/05/04    6
Nic    12      A    2013/06/02    7
Jen    15      A    2013/06/09    8

Expected Output :

Name   Age    Type   Date         Rank
Mar    13      A    2013/05/04    1
Nic    12      A    2013/06/02    2
Jen    15      A    2013/06/09    3

Answer 1

Try

WITH qry AS 
(
  SELECT "Name", "Age", "Type", "Date", 
         ROW_NUMBER() OVER (PARTITION BY "Type" ORDER BY "Date") rank
  FROM TableA
)
SELECT "Name", "Age", "Type", "Date"
  FROM qry
 WHERE rank = 1

Output:

| NAME | AGE | TYPE |                            DATE |
-------------------------------------------------------
|  Ben |  12 |    A | February, 01 2013 00:00:00+0000 |
|  Zed |  13 |    B |    March, 09 2013 00:00:00+0000 |

Here is SQLFiddle demo

Answer 2

There is another possibility: You could wrap it in a subquery:

select
    t.*
  from
    (SELECT "Name", "Age", "Type", "Date", 
         ROW_NUMBER() OVER (PARTITION BY "Type" ORDER BY "Date") rank
       FROM TableA
     ) t
  where
    rank = 1

"Sub queries" and "common table expressions (with)" behave differently, so have a look at these approaches. It depends on your case, if one is mandatory (dealing with side-effects) or one is faster. In case of Oracle, there is even a materialize hint. "Per SQL standard, CTEs offer an optimization fence feature" (this is an adapted quote from here).