如何让第一行过滤掉一些值？我使用row_number()(按名称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
解决方法
尝试
WITH qry AS ( SELECT "Name","Age","Type","Date",ROW_NUMBER() OVER (PARTITION BY "Type" ORDER BY "Date") rank FROM TableA ) SELECT "Name","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演示
