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.

Nominated

Thank you for my coworkers at Solita making this happend. I got just invited and signed to receive the Oracle ACE award. Proud to be credited to the community as Julian, Tuomas and Heli from Finland earlier. From now on I am allowed to use ACE logo along.

We will continue delivering success to our customers. Some highlights will continue popping up here in this blog.

Pascal matrix

Just read about yet another SQL Challenge pointing to the original request to solve Pascal matrix with ANSI SQL. Well this following solution is maybe braking the rules, but illustrates an Oracle 1Xy wishlist feature. Mixing analytic functions and recursive subquery factoring. The solution is using PostgreSQL 9.1 capabilities.

with recursive n (u) as (
select 1 
union all
select n.u+1 
  from n
 where n.u < 8
), q as (
select n.u v, m.u w 
  from n, n m 
), r (v,w,s,d,e) as (
select v,w, 1::bigint,w::bigint,sum(w)over(order by w)::bigint
  from q
 where v = 1
union all
select q.v,q.w
      ,r.d
      ,r.e
      ,sum(r.e)over(order by r.w)::bigint
  from r 
 inner join q 
    on r.w=q.w and r.v+1=q.v
)
select v,w,s
  from r
;

1;1;1
1;2;1
1;3;1
1;4;1
1;5;1
1;6;1
1;7;1
1;8;1
2;1;1
2;2;2
2;3;3
2;4;4
2;5;5
2;6;6
2;7;7
2;8;8
3;1;1
3;2;3
3;3;6
3;4;10
3;5;15
3;6;21
3;7;28
3;8;36
4;1;1
4;2;4
4;3;10
4;4;20
4;5;35
4;6;56
4;7;84
4;8;120
5;1;1
5;2;5
5;3;15
5;4;35
5;5;70
5;6;126
5;7;210
5;8;330
6;1;1
6;2;6
6;3;21
6;4;56
6;5;126
6;6;252
6;7;462
6;8;792
7;1;1
7;2;7
7;3;28
7;4;84
7;5;210
7;6;462
7;7;924
7;8;1716
8;1;1
8;2;8
8;3;36
8;4;120
8;5;330
8;6;792
8;7;1716
8;8;3432