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