The Latests

It is nice to share information when there are opportunities to receive information. Had just a nice after work sauna, introduction and chat about Data Vault modeling technique.

In following rehearsal there is a part of a satellite table with some data. And there is a request to receive the latest. It is possible to receive the information with only one reference to the table.

create table sat_dv(
  dv_id number not null
, dv_load_dt date not null
, dv_info varchar2(200)
);

insert into sat_dv
select mod(level,5)
     , sysdate-level
     , level||'info' 
 from dual connect by level < 200;

select satelite.* 
  from sat_dv satelite
  inner join ( 
     select dv_id, max(dv_load_dt) max_dv_load_dt
       from sat_dv 
      group by dv_id) latest 
  on latest.dv_id= satelite.dv_id 
 and latest.max_dv_load_dt = satelite.dv_load_dt
;

"DV_ID" "DV_LOAD_DT" "DV_INFO"
1 26.06.2012 "1info"
2 25.06.2012 "2info"
3 24.06.2012 "3info"
4 23.06.2012 "4info"
0 22.06.2012 "5info"

Lazy as I am. I let you to do the work. Two examples to use analytic or keep first aggregate approach are introduced in a earlier post. There I did not introduce this "browsing the table twice" approach. Although unfortunately quite often this is the starting point to query tuning.