need SQL Query Help

I need help with a query,
i have two tables,
name Qty orderID Store
Paper 1000 101 New York
Paper 2000 101 Chicago
Pen 2000 102 New York
Pen 5000 102 Chicago
table two
Purchase
orderID Qty price Date
101 100 $4 7/1/05
101 200 $5 7/15/05
101 360 $3.6 8/5/05
101 150 $5.2 8/30/05
102 400 $6 7/2/05
102 300 $6.5 7/12/05
102 500 $5 8/3/05
for the result, I only want to know the last purchase on each like:
Paper 150 $ 5.2 8/30/05
Pen 500 $ 5. 8/3/05
What should I do in this case? Thanks a lot for your help.
MichaelThe following example may get you going. It does not handle time,
change the convert code accordingly.
Since both tables have multiple equivalent orderids, a composite where
will need to be built to ensure uniqueness.
select t1.name, t2.qty, t2.price, t2.date
from table2 t2
inner join table1 t1 on t1.orderid = t2.orderid
where str(t2.orderid) + convert(varchar, t2.date, 112) in
(
select top 1 str(t2.orderid) + convert(varchar, t2.date, 112)
from table2 t2
order by date desc
)
mli wrote:
> I need help with a query,
> i have two tables,
> name Qty orderID Store
> Paper 1000 101 New York
> Paper 2000 101 Chicago
> Pen 2000 102 New York
> Pen 5000 102 Chicago
> table two
> Purchase
> orderID Qty price Date
> 101 100 $4 7/1/05
> 101 200 $5 7/15/05
> 101 360 $3.6 8/5/05
> 101 150 $5.2 8/30/05
> 102 400 $6 7/2/05
> 102 300 $6.5 7/12/05
> 102 500 $5 8/3/05
> for the result, I only want to know the last purchase on each like:
> Paper 150 $ 5.2 8/30/05
> Pen 500 $ 5. 8/3/05
> What should I do in this case? Thanks a lot for your help.
> Michael