I need to write a query to find out all the parent items which has a MAX of Qty and Max of Childitem. Here the process is like, first we need to get the MAX Qty per Parent and for this Qty get the MAX of Childitem.
Parent Item ChildItem Qty
62338-87468-00 62338-77961-20 45.333
62338-87468-00 62338-77961-18 45.333
62338-87468-00 62338-84683-21 45.333
62338-87468-00 62338-84683-18 46
62338-87468-00 62338-87135-01 46
62338-87468-00 62338-87135-00 45
From the above example, there are two childitems of the MAX Qty. so the final output should be 62338-87468-00(parent), 62338-87135-01(Child), 46(Qty). It means there should be only one row per each parent item.
Help would be greatly appreciated.