>Hi all
>
>

>select itemcode,sum(qty) as totqty,sum(totalcost) as totalcost,min(expiry) as nxtexpire
>from (select itemno,itemcode,expiry,qty,totalcost from items where qty > 0) a  group by itemcode
>

>
>How to get the itemno that has the min(expiry) ?
>
>
>TIA

if

select c.itemno,b1.* from
(select a.itemcode,sum(qty) as totqty,sum(totalcost) as totalcost,min(expiry) as nxtexpire
from (select itemno,itemcode,expiry,qty,totalcost from items where qty > 0) a  group by a.itemcode) b1
left join (select itemno,itemcode,expiry from items where qty > 0) c on c.expiry = b1.nxtexpire and c.itemcode = b1.itemcode

but 2 or more recno has the same expiry then error :(