在Oracle中选择组的前n个元素 [英] Selecting top n elements of a group in Oracle

问题描述

我有一个包含名称，值，时间列的Oracle表.该表基本上是用于记录日志的目的，用于存储对特定名称所做的更改，先前的值是什么以及更改时间.

I have an Oracle table which has a name,value,time columns.Basically the table is for logging purposes to store what are the changes made to a particular name,what was the previous value and what time the change was made.

我需要制定一个查询来获取特定名称的前n个更改，并且输出应具有表中的所有名称. 有帮助/建议吗?

I need to formulate a query to fetch the top n changes for a particular name,and the output should have all the names in the table. Any help/suggesstions?

Name         Value     Time
Harish       Pass      1-Nov-2011
Ravi         Fail      2-Nov-2011
Harish       Absent    31-Oct-2011
Harish       Attended  31-Aug-2011 
Harish       Present   31-Jul-2011

我需要选择11月1日，10月31日，8月31日和Ravi的Harish详细信息.

I need to select details of Harish on 1st Nov,Oct 31st,31st Aug and Ravi.

推荐答案

这是您要的吗?

我的测试设置:

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:00
X                Test2                01-Jan-2011 13:00:00
Y                Test7                04-Jan-2011 12:00:00
Y                Test6                03-Jan-2011 12:00:00

SQL> 

这篇关于在Oracle中选择组的前n个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！