CODEWITHSUNDEEP
sqloracle-databasetimestamp
skrk
skrk

Reputation: 65

Oracle max function on timestamp datatype

I want to take a max date from a timestamp datatype in oracle

my data look like this

id  updated_date
--  -------------------
1   2011-12-21 10:34:24 
1   2011-12-21 09:52:15
1   2011-12-21 10:02:49

Since all the date are same but timestamp is difference the max function bringing all the 3 rows instead i want to see the max date with latest timestamp for each ID like below

Select MAX(updated_date) update_date, id FROM id_table group by id;
id  updated_date
--  -------------------
1   2011-12-21 10:34:24

thanks in advance

The table as 10000 plus rows, the data looks like below 

 id  updated_date
 --  -------------------
 1   2011-12-21 10:34:24 
 1   2011-12-21 09:52:15
 1   2011-12-21 10:02:49
 2   2011-13-21 10:34:24 
 2   2011-13-21 09:52:15
 2   2011-13-21 10:02:49
 3   2011-14-21 10:34:24 
 3   2011-14-21 09:52:15
 3   2011-14-21 10:02:49

On select i want to see 1 row per id no duplicates Select MAX(updated_date) update_date, id FROM id_table group by id;

id  updated_date
 --  -------------------
 1   2011-12-21 10:34:24 
 2   2011-13-21 10:34:24 
 3   2011-14-21 10:34:24

Upvotes: 0

Views: 11310

Answers (2)

Kristian Saksen
Kristian Saksen

Reputation: 114

If you want max from your table you must skip the group by predicate. Your group by predicate instructs Oracle to give you the max value for each group - which -in your case is for each id.

try this: 

Select MAX(updated_date) update_date FROM id_table

Doing a little test on this issue 

create table test_ts_1 (tst_id number , my_timestamp timestamp); 

declare
begin
  for i in 1..10000 loop
    for j in 1..10 loop
      insert into test_ts_1 (tst_id, my_timestamp) values (i, systimestamp); 
    end loop; 
  end loop; 
end; 
/

select tst_id, max(my_timestamp), min(my_timestamp) from test_ts_1 group by tst_id order by tst_id asc; 
***********************************
1   04.12.2018 23.01.31,364979000   04.12.2018 23.01.31,364322000
2   04.12.2018 23.01.31,365596000   04.12.2018 23.01.31,365032000
3   04.12.2018 23.01.31,366284000   04.12.2018 23.01.31,365662000
4   04.12.2018 23.01.31,366890000   04.12.2018 23.01.31,366350000
5   04.12.2018 23.01.31,367468000   04.12.2018 23.01.31,366942000
6   04.12.2018 23.01.31,368050000   04.12.2018 23.01.31,367518000
7   04.12.2018 23.01.31,368728000   04.12.2018 23.01.31,368118000
8   04.12.2018 23.01.31,369366000   04.12.2018 23.01.31,368797000
9   04.12.2018 23.01.31,369992000   04.12.2018 23.01.31,369430000
10  04.12.2018 23.01.31,370713000   04.12.2018 23.01.31,370055000
11  04.12.2018 23.01.31,371395000   04.12.2018 23.01.31,370784000
12  04.12.2018 23.01.31,372117000   04.12.2018 23.01.31,371470000
13  04.12.2018 23.01.31,372843000   04.12.2018 23.01.31,372174000
14  04.12.2018 23.01.31,373635000   04.12.2018 23.01.31,372919000
15  04.12.2018 23.01.31,374391000   04.12.2018 23.01.31,373710000
16  04.12.2018 23.01.31,375176000   04.12.2018 23.01.31,374467000

As shown timestamp columns works perfectly using group by and aggregate functions.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31686

You may use the FIRST aggregate function or row_number().

1.

SELECT MAX(id) KEEP(DENSE_RANK FIRST ORDER BY updated_date DESC) AS id,
       MAX(updated_date) update_date
FROM id_table;

2. 

SELECT id,
       updated_date
FROM (
     SELECT i.*,
            ROW_NUMBER() OVER(
                 ORDER BY updated_date DESC
            ) AS rn
     FROM id_table i
)
WHERE rn = 1;

Demo

Bonus ( for Oracle 12c and above) :

select * from id_table 
ORDER BY updated_date DESC FETCH FIRST 1 ROWS ONLY

Upvotes: 2

Related Questions