Updating group rows with max value for the group

• Mark Harley

  SSCrazy

  Points: 2259

  More actions

  November 17, 2015 at 11:43 am

  #309698

  I have a table with line items from many orders, broken down into sub-orders. Each I need to generate a unique guid for each sub-order, as well as calculate which item was ordered in the largest quantity and set that as the "key item" for the sub-order. The table should look like this:

  client|office|day|month|year|sub-order|item|amount|key_item|sub_order_guid

  1|1|1|1|2015|1||12875|100|12875||21FA413A-98AE-48FC-943B-504856A1E4AD

  1|1|5|2|2015|1||16784|63|16784||B22784B7-274D-4452-AB78-A498D69A91BC

  2|1|7|2|2015|1||13547|75|13547||1A86C3D4-050F-4DE5-B340-F58D461A70A0

  1|2|13|2|2015|1||13547|43|78467||BC859235-7DE3-43D0-A817-CFAF029A604E

  1|2|13|2|2015|1||78467|104|78467||BC859235-7DE3-43D0-A817-CFAF029A604E

  1|2|13|2|2015|2||78684|71|78684||DE4C3A38-8ED2-4B9C-A9B9-B6010B62BD9E

  How do I go about grouping the sub-order lines together and determining which item was purchased the most? And then how do I generate the guid to assign to the entire sub-order? Am I explaining this sufficiently?

  I've tried the following, but it isn't working as I expected:

  select client_id, office_id, start_year, start_month, start_day, sub_order_number, item_id, max(item_amount)

  over(partition by client_id, office_id, start_year, start_month, start_day, sub_order_number) as 'Max'

  from orders

  order by start_year, start_month, start_day

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  November 17, 2015 at 1:16 pm

  #1840476

  Try the following:

  SELECT client_id, office_id, start_year, start_month, start_day, sub_order_number, item_id

  , LAST_VALUE(sub_order_guid)

  OVER(

  PARTITION BY client_id, office_id, start_year, start_month, start_day, sub_order_number

  ORDER BY amount, sub_order_number

  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

  ) AS order_guid

  FROM orders

  ORDER BY start_year, start_month, start_day

  Since it's possible for two items to have the same amount, I added the sub_order_num to the sort to make it unique.

  If that doesn't work, then please provide the expected results.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA