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

  • 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,

  • 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]

  • Thanks Buddy this helped me a lot to complete my Proc.

  • Viewing 4 posts - 1 through 3 (of 3 total)

    You must be logged in to reply to this topic. Login to reply