Hi all.

I have 2 tables, one is basically a part list, the other is a price history. The price history has an effective date. All tables and variable names have been changed to protect the identities of the innocent <g>.

Some parts may not have any pricing, so I used an outer join.

The query I want should give the "current" price ... as of the current date/time.

This works when the newer record has a higher record number in the table, but not when I entered the data in reverse. What do I have to do to make it always dependant on the effective date, not on the record sequence? I don't plan to allow the client to edit old records, but I might try to recycle deleted records and this will be a problem.

select ;
parts.part_pk, ;
max(nvl(price_history.effective,dtot({}))) as Effective_Date, ;
nvl(price_history.Price,0000000.00) as Current_Price ;
from parts ;
left outer join price_history on ;
parts.part_pk = price_history.part_fk ;
group by ;
parts.part_pk

Thanks!

Mike