以MAX(TIMESTAMP)分组 [英] Group By with MAX(TIMESTAMP)

问题描述

我有一个表格，其中显示字母ID和年份并创建了时间戳.我必须列出每年的最新时间戳.

I have a table showing letter id with year and created timestamp.I have to list the latest timestamp for every year.

例如:这是我的数据.

  Letter ID YEAR               TIMESTAMP
  1411      2013     17-NOV-14 09.18.01.000000000 AM
  1412      2013     16-NOV-14 09.18.01.000000000 AM
  1413      2013     15-NOV-14 09.18.01.000000000 AM
  1414      2013     14-NOV-14 09.18.01.000000000 AM    
  1415      2013     13-NOV-14 09.18.01.000000000 AM
  1416      2012     17-NOV-14 10.18.01.000000000 AM
  1417      2012     16-NOV-14 10.18.01.000000000 AM

期望的结果应该是:

Letter ID   YEAR     TIMESTAMP
  1411      2013     17-NOV-14 09.18.01.000000000 AM
  1416      2012     17-NOV-14 10.18.01.000000000 AM

请让我解决这个问题...

Please get me to solve this...

推荐答案

另一种方法:

SELECT DISTINCT
       letter_id
      ,year
      ,MAX("TIMESTAMP") OVER (PARTITION BY letter_id, year) AS "TIMESTAMP"
FROM   my_data;

请注意，TIMESTAMP是Oracle中的保留字，因此我认为您的列需要以"TIMESTAMP"分隔.

Note that TIMESTAMP is a reserved word in Oracle so I assume your column needs to be delimited as "TIMESTAMP".

这篇关于以MAX(TIMESTAMP)分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！