SQL Query to find out MAX Qty and MAX Childitem of a parent item

• yvbchowdary

  Valued Member

  Points: 56

  More actions

  July 16, 2013 at 3:38 pm

  #277436

  Hi Folks,

  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 ItemChildItem Qty

  62338-87468-0062338-77961-2045.333

  62338-87468-0062338-77961-1845.333

  62338-87468-0062338-84683-2145.333

  62338-87468-0062338-84683-1846

  62338-87468-0062338-87135-0146

  62338-87468-0062338-87135-0045

  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.

  Thank you,

  Venu Babu,

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 16, 2013 at 3:50 pm

  #1633028

  It helps if you provide your data in a re-usable format , such as this:

  with data(ParentItem,ChildItem,Qty)

  as

  (

  select '2338-87468-00', '62338-77961-20', '45.333' union all

  select '2338-87468-00', '62338-77961-18', '45.333' union all

  select '2338-87468-00', '62338-84683-21', '45.333' union all

  select '2338-87468-00', '62338-84683-18', '46' union all

  select '2338-87468-00', '62338-87135-01', '46' union all

  select '2338-87468-00', '62338-87135-00', '45'

  ),

  -- Here is the query you need

  RowNumbered(ParentItem,ChildItem,Qty,Rn) as

  (

  select *,row_number() over(partition by ParentItem order by Qty desc,ChildItem desc) as Rn

  from data

  )

  select ParentItem,ChildItem,Qty

  from RowNumbered

  where Rn=1

  I have done it for you because you are new here 🙂

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]