Reputation: 1473
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
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
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
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