Re: Finding latest date given a date

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Apr 2003 08:25:10 GMT
Message-ID: <b6m3t6$6ruch$1@ID-82536.news.dfncis.de>

> I have a database table that has millions of records.
> For each unique position(client, item_id, pos_basis), I need to find
> the latest record given a date.
>
> CLIENT DATE ITEM_ID POS_BASIS QTY
> A 20/3/2003 1001 3 1000
> A 21/2/2003 1001 3 10
> A 21/3/2003 1001 4 1000
> A 23/2/2003 1001 4 10
> B 20/3/2003 1001 3 1000
> B 21/2/2003 1002 3 10
> B 21/3/2003 1003 3 1000
> B 23/2/2003 1004 3 10
>
>
> Result for 1/3/2003 for above table should be
> CLIENT DATE ITEM_ID POS_BASIS QTY
> A 21/2/2003 1001 3 10
> A 23/2/2003 1001 4 10
> B 21/2/2003 1002 3 10
> B 23/2/2003 1004 3 10
>
>

Which version of Oracle are you using?

Anyway this would do it on 9i and I believe on 8i as well:

alter session set nls_date_format = 'dd/mm/yyyy';

create table a_ (
  client char(1),
  dt date,
  item_id number,
  pos_basis number,
  qty number
);

insert into a_ values('A','20/3/2003',1001,3,1000);
insert into a_ values('A','21/2/2003',1001,3,  10);
insert into a_ values('A','21/3/2003',1001,4,1000);
insert into a_ values('A','23/2/2003',1001,4,  10);
insert into a_ values('B','20/3/2003',1001,3,1000);
insert into a_ values('B','21/2/2003',1002,3,  10);
insert into a_ values('B','21/3/2003',1003,3,1000);
insert into a_ values('B','23/2/2003',1004,3,  10);

select
  client, dt, item_id, pos_basis, qty
from (
  select
    client, dt, item_id, pos_basis, qty ,     rank() over (partition by dt,item_id,pos_basis

                   order by dt desc) r

  from
    a_
  where
    dt < '1/3/2003'
)
where r = 1;

> Problem I have is that as the number of records increase, my query
> would get slower and slower.
> Any solutions to this?

This doesn't surprise me. After all, oracle must do more work.

A solution would be to store the dates precalculated in a table.

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sat Apr 05 2003 - 02:25:10 CST