Select rows where field not duplicated

[PapaSmurf]

I have a simple select statement:

SELECT Date, Time, Order, Part, Price
FROM Mytable
ORDER BY Date DESC, Time DESC

The table contains deliveries made to our stores.

This may return rows with the same part number for different dates and times. I only wish to return the latest transaction for any particular item, so as per the grouping I want the first row occurrance of each item, ignoring the rest.

Is there a simple way of doing this? Thanks in advance

 

[Eyespi20]

try this

select
lastdate = max(date),
lasttime = max(time),
part
into
##temptable
from
mytable
group by mytable.part

alter table ##temptable add order varchar(15), price float

update ##temptable set order = mytable.order, price = mytable.price
from
##temptable, mytable
where
##temptable.part = mytable.part
and
##temptable.lastdate = mytable.date
and
##temptable.lasttime = mytable.time

select * from ##temptable

 

[PapaSmurf]

Thanks Eyespi20, but I get an error

Server: Msg 207, Level 16, State 1, Line 13
Invalid column name 'PO_NUMBER'.

My new statement with real table and field names is:

select
lastdate = max(TRAN_DATE),
lasttime = max(TRAN_TIME),
ITEM
into
##temptable
from
Hanover_Lastpop1
group by Hanover_Lastpop1.ITEM

alter table ##temptable add PO_NUMBER varchar(15), UNIT_PRICE float

update ##temptable set PO_NUMBER = Hanover_Lastpop1.PO_NUMBER, UNIT_PRICE = Hanover_Lastpop1.UNIT_PRICE
from
##temptable, Hanover_Lastpop1
where
##temptable.ITEM = Hanover_Lastpop1.ITEM
and
##temptable.lastdate = Hanover_Lastpop1.TRAN_DATE
and
##temptable.lasttime = Hanover_Lastpop1.TRAN_TIME

select * from ##temptable

..So something wrong with the ALTER TABLE line, but my knowledge isn't sufficent to figure out what!

 

[PapaSmurf]

actually, the UPDATE line is causing the problem...