sql - Oracle SQL : Getting the first row with filters

Question

如何让第一行过滤掉一些值？我使用 row_number() over(partition by Name ORDER BY Date) 来获取订单(见下面的例子)。但我需要，排名将从最后一次出现 Type = B 开始(见预期输出)

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

例如 :

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

预期输出:

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

尝试

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

输出:

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

这是 SQLFiddle 演示