Select most recent record

[spiff2002]

I have a table with sales history more or less like this:


Date Item# Customer# qty price
10/02/2003 0001 034 2 20.00
10/03/2003 0002 048 3 30.00
09/03/2003 0002 062 4 30.00
08/15/2003 0004 045 3 15.00
02/20/2003 0007 035 1 25.00
01/31/2003 0001 038 3 20.00

I need to run a slaes report based on Item# where i will show the most recent sale of an item and going back one Year. i.e. form the table above the result will be:

10/02/2003 0001 034 2 20.00
10/03/2003 0002 048 3 30.00
08/15/2003 0004 045 3 15.00
02/20/2003 0007 035 1 25.00

The query will give as a result the most recent sale for all the items for the whole year.

Any one has any ideas???

Thank you in advance for taking the time to read this.

 

[jimbopalmer]

Select a.trans_date, a.item_no, a.customer_no, a.trans_qty, a.trans_price from trans_lines a where a.trans_date = (select Max(b.transdate) from trans_lines b where a.item_no = b.item_no) and a.trans_date > sysdate - 365;

if trans_date is accurate that should be fine, if you truncate trans_date to the day, there can be dups.

I tried to remain child-like, all I acheived was childish.