Finding latest date given a date

From: Developer <wahkiat_at_hotmail.com>
Date: 4 Apr 2003 19:58:59 -0800
Message-ID: <29590346.0304041958.4a9c0055@posting.google.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 Received on Fri Apr 04 2003 - 21:58:59 CST