在Oracle中选择组的前n个元素

这是你所追求的吗？

我的测试设置：

SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:Mi:SS';Session altered.SQL> drop table so_test;Table dropped.SQL> create table so_test  (  2    n varchar2(32)  3  , v varchar2(32)  4  , t date );Table created.SQL> SQL> insert into so_test values ( 'X' , 'Test1', to_date('01-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL> insert into so_test values ( 'X' , 'Test2', to_date('01-Jan-2011 13:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL> insert into so_test values ( 'X' , 'Test3', to_date('01-Jan-2011 14:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL> insert into so_test values ( 'Y' , 'Test5', to_date('02-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL> insert into so_test values ( 'Y' , 'Test6', to_date('03-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL> insert into so_test values ( 'Y' , 'Test7', to_date('04-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );1 row created.SQL>

这是查询：

SQL> select n,v,t from (  2  select n, v , t , rank() over ( partition by n order by t desc) r  3  from so_test  4  ) where r <= 2;N     V     T-------------------------------- -------------------------------- --------------------X     Test3     01-Jan-2011 14:00:00X     Test2     01-Jan-2011 13:00:00Y     Test7     04-Jan-2011 12:00:00Y     Test6     03-Jan-2011 12:00:00SQL>