joe
joe

Reputation: 1473

Max function in Oracle 12

I have two records below. I was trying to pull the latest record based from the Sold_DT, which would give me the record with 7 in Item column.

    Store       Sold_DT                      Item
CVS             1/22/2017 12:05:00 AM          5
CVS_S_Eleven    1/22/2017 10:41:00 AM          7

Here is my attempted query in Oracle:

select 
store, 
max(sold_dt) as max_dt, 
item
from temp
group by store, item

Would someone please point me to the correct direction so that I can return only the latest Sold_DT? which is only this:

  Store     Sold_DT                                  Item
    CVS_S_Eleven    1/22/2017 10:41:00 AM            7

Upvotes: 1

Views: 65

Answers (3)

Bobby Durrett
Bobby Durrett

Reputation: 1293

I wasn't sure if you only wanted one row if there are two with the same date and time. I assumed that you only want one. Of course, in this case there is only one row with max date anyway.

SQL> 
SQL> drop table temp;

Table dropped.

SQL> 
SQL> create table temp
  2  (
  3  Store varchar2(12),
  4  Sold_DT date,
  5  Item number
  6  );

Table created.

SQL> 
SQL> insert into temp values ('CVS',to_date('01/22/2017 12:05:00 AM','MM/DD/YYYY HH:MI:SS AM'),5);

1 row created.

SQL> insert into temp values ('CVS_S_Eleven',to_date('01/22/2017 10:41:00 AM','MM/DD/YYYY HH:MI:SS AM'),7);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select
  2  store,
  3  Sold_DT,
  4  item
  5  from temp
  6  where
  7  Sold_DT = (select max(Sold_DT) from temp) and
  8  rownum < 2;

STORE        SOLD_DT         ITEM                                               
------------ --------- ----------                                               
CVS_S_Eleven 22-JAN-17          7                                               

SQL> 
SQL> spool off

Upvotes: 1

Rams
Rams

Reputation: 2169

You can take max of sold date from subquery like below and join with main table on that date value

select t1.*  from 
    from temp t1 join (select
    max(sold_dt) as max_dt, item
    from temp)  t2 on t1.Max_dt=t2sold_dt

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

Try the MIN/MAX with KEEP. As mathguy points out, if you have two stores tied for most recent, it will get tricky and you are best to add a tiebreaker if you want a single consistent record, or use another technique if you want all ties.

select 
   max(store) keep (DENSE_RANK FIRST ORDER BY sold_dt desc) max_store, 
   max(sold_dt) as max_dt, 
   max(item) keep (DENSE_RANK FIRST ORDER BY sold_dt desc) max_item
from temp

Upvotes: 1

Related Questions