HERE IS MY DATA

PART PRICE DATE
1 , 1 , 1-30-05
1 , 1 , 1-14-05
1 , 2 , 1-22-05
1 , 2 , 1-26-05


when i try to pull only the record for the latest date, since there is 2 prices it brings in 2 records since i am using the MAX function on the date but grouping distiguishes price of 1 and price of 2 separately. can anyone help with how to tell it i only want the latest record for part #1 no matter the price but display the price with that record? thanks GRT

This is simple SQL, did you try using the MAX function?:

Code:

SELECT * FROM MYDATA P
 WHERE PART=1
     AND PRICE_DATE = (
SELECT MAX(PRICE_DATE) FROM MYDATA D
 WHERE D.PART = P.PART);

Also, i recommend you read this or this or this.

yes i tried the Max function but the data i supplied is just an example i am working with over 3000 sku's with a total records count exceeding 250k. so the option to select based on a part number is not there ans so the group by function required with a max statement does not work
here is the actual statement

select
ii.catalog_no CAT,
ih.name NAME,
II.sale_unit_price PRICE,
max(ih.invoice_date) iDATE
from ifsapp.customer_order_inv_item ii,
ifsapp.customer_order_inv_head ih
where
ifsapp.ii.invoice_id = ih.invoice_id
GROUP BY Ii.catalog_no,
ih.name,
II.sale_unit_price,
ih.invoice_date
ORDER BY IH.INVOICE_DATE DESC

the use of a Max statement requires a group by statement which segregates also by customer name and the different prices so i get multiple records not just the final record.....

thanks GRT