Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query to find out MAX Qty and MAX Childitem of a parent item Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 27, 2014 1:14 AM
Points: 7, Visits: 88
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 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.

Thank you,
Venu Babu,
Post #1474315
Posted Tuesday, July 16, 2013 3:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,816, Visits: 5,911
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1474318
    Posted Thursday, July 18, 2013 5:25 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, November 27, 2014 1:14 AM
    Points: 7, Visits: 88
    Thanks Buddy this helped me a lot to complete my Proc.
    Post #1474980
    Posted Monday, February 3, 2014 4:07 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Friday, February 7, 2014 4:19 PM
    Points: 5, Visits: 5
    thanks for the info
    qualities of a leader D&P DNP dandp




    customer service skills list hr source consulting human resources internships project proposal template superior staffing
    Post #1537548
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse