Group By with MAX(TIMESTAMP)

Question

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

For example: This is my DATA.

  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

The desired result should be:

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...

score 0 · Answer 1 · edited Nov 19 '14 at 07:47

0

select * from (
select Letter_ID,YEAR,TIMESTAMP,
       row_number()over(partition by YEAR order by TIMESTAMP desc) RN from DATA
) WHERE RN = 1

Hope helps you.

edited Nov 19 '14 at 07:47

answered Nov 19 '14 at 07:46

VikiYang

233
1
10

score 0 · Answer 2 · answered Nov 19 '14 at 07:57

Try this:

SELECT t2.LETTER_ID, t1.YEAR, t1.TIMESTAMP
  FROM (SELECT YEAR, MAX(TIMESTAMP) as MAX_TIMESTAMP
          FROM YOUR_TABLE -- change the table name to yours
         GROUP BY YEAR)t1
  JOIN YOUR_TABLE t2 -- change the table name to yours
    ON t2.YEAR = t1.YEAR
   AND t2.TIMESTAMP = MAX_TIMESTAMP

This query will run on any database.

score 0 · Answer 3 · answered Nov 19 '14 at 08:04

Another method:

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

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

Group By with MAX(TIMESTAMP)

3 Answers3

Linked