Get a value from MAX row

[WurzelGummidge]

If i have a purchase order table with many rows for the same product, how do i return the COST of a product based on the MAX of date_purchased - ie:

PRODUCT DATE_OF_PURCHASE COST
abc123 10-02-2003 21.20
abc123 25-05-2003 20.00
abc123 17-03-2003 22.60
abc123 02-08-2003 23.10

etc..
I would like to return the COST from the LAST purchased date

 

[Glyndwr]

create table #t (
 prod varchar(10)
,purch_date datetime
,cost money
,Salesman varchar(10)
)
go
insert into #t values ('abc' ,'10 jan 2003',50.03,'Tom')
insert into #t values ('abc' ,'14 jan 2003',48.95,'Dick')
insert into #t values ('abc' ,'15 jan 2003',67.09,'Harry')
insert into #t values ('xyz' ,'16 jan 2003',45.96,'Peter')
insert into #t values ('xyz' ,'16 jan 2003',47.96,'Peter')
go
select prod, purch_date,cost ,salesman
from #t t
where purch_date = ( select max(t2.purch_date) from #t t2 where t.prod =t2.prod)

prod purch_date cost salesman
---------- ------------------------------------------------------ --------------------- ----------
xyz 2003-01-16 00:00:00.000 45.9600 Peter
xyz 2003-01-16 00:00:00.000 47.9600 Peter
abc 2003-01-15 00:00:00.000 67.0900 Harry


I have added in a duplicate date for completeness - how do you want these dealt with ? or don't they occur

 

[iker3000]

I would make it this way:

SELECT
Table_1.PRODUCT,
AVG(Table_1.COST)
FROM
Table_1
INNER JOIN
(SELECT
PRODUCT,
MAX(DATE_OF_PURCHASE) AS DATE_OF_PURCHASE
FROM
Table_1
GROUP BY
PRODUCT) AS Max_date_table
ON Max_date_table.PRODUCT = Table_1.PRODUCT
AND Max_date_table.DATE_OF_PURCHASE =
Table_1.DATE_OF_PURCHASE
GROUP BY
TABLE1.PRODUCT

I think it is more efficient, but it is also more complicated. You could handle duplicate dates by calculating the average cost.

Iker

 

[WurzelGummidge]

Iker -

I like the concept but cannot apply it to my actual fields (don't completely understand all syntax)

could you possibly use my actual table / field names:
table = scheme.stquem
fields = unit_cost, date_received, product

i know this sounds stupid, but when i attempted to change your example to my actual field names, it seemed to bugger up on the aliases !
Thanks ...

 

[JamesLean]

SELECT product, date_of_purchase, cost
FROM stquem s
WHERE date_of_purchase = (
  SELECT MAX(date_of_purchase) FROM stquem
  WHERE product = s.product
)

--James

 

[iker3000]

SELECT
stquem.product,
AVG(stquem.unit_cost)
FROM
stquem
INNER JOIN
(SELECT
product,
MAX(date_received) AS date_received,
FROM
stquem
GROUP BY
product) AS Max_date_table
ON Max_date_table.product = stquem.product
AND Max_date_table.date_received =
stquem.date_received
GROUP BY
stquem.product

IKER