[SOLVED] getting latest date in an item

[gardan06]

i currently have this code:

select c.sku,c.sku_desc,a.po_no,b.actual_vendor_code as vendor_code,unit_cost,b.receivedate as issuedbydate from sku c left outer join purchase_detail a on (c.sku=a.sku) inner join purchase_hdr b on (a.po_no=b.po_no) where b.po_status='RR_PO' and c.dept_code='2' order by c.sku,b.receivedate desc

 

but i want to group them per sku, so i tried:

select c.sku,c.sku_desc,a.po_no,b.actual_vendor_code as vendor_code,unit_cost,b.receivedate as issuedbydate from sku c left outer join purchase_detail a on (c.sku=a.sku) inner join purchase_hdr b on (a.po_no=b.po_no) where b.po_status='RR_PO' and c.dept_code='2' group by c.sku order by c.sku,b.receivedate desc

 

the problem was it didn't recognize the receivedate ordering so while it did show only one SKU item, it also showed the earliest item, not the latest.

 

i also tried:

select distinct(c.sku),c.sku_desc,a.po_no,b.actual_vendor_code as vendor_code,unit_cost,b.receivedate as issuedbydate from sku c left outer join purchase_detail a on (c.sku=a.sku) inner join purchase_hdr b on (a.po_no=b.po_no) where b.po_status='RR_PO' and c.dept_code='2' order by c.sku,b.receivedate desc

 

but the results were the same as the 1st one.

 

would anyone be kind enough to show me how to code the query in a way that only one SKU item is shown and it should be the latest one.

 

your help is greatly appreciated. thank you.

[fenway]

What was the solution?