Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news-feed01.roc.ny.frontiernet.net!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: wahkiat@hotmail.com (Developer)
Newsgroups: comp.databases.oracle.misc
Subject: Finding latest date given a date
Date: 4 Apr 2003 19:58:59 -0800
Organization: http://groups.google.com/
Lines: 28
Message-ID: <29590346.0304041958.4a9c0055@posting.google.com>
NNTP-Posting-Host: 165.21.83.230
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1049515139 31482 127.0.0.1 (5 Apr 2003 03:58:59 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 Apr 2003 03:58:59 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:95879
X-Received-Date: Fri, 04 Apr 2003 20:58:41 MST (news.easynews.com)

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


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

Rgds