select * from Table where ID in (select top 1 ID from Table where ID=3 order by NAME desc)
If you not solve paste you query we can help on that.
I have create Demo As like your query refer this:
This are my tables and their fields
UnitID(int) UnitTypeID(int) UnitName(varchar)
ProductID(int) ProductName(varchar) UnitID(int) UnitTypeID(int)
This is query for you solution:
select Distinct UT.UnitTypeID,UT.UnitTypeName,U.UnitID,U.UnitName,P.ProductID,P.ProductName,P.UnitId from UnitTypes UT
inner join Units U on U.UnitTypeID=UT.UnitTypeID
inner join Products P on P.UnitId=U.UnitID and P.ProductID in (select MAX(ProductID) from Products where p.UnitId=UnitID)
order by U.UnitID
Accept as answer and vote is solve your problem.