Hive/sql Bundling Columns For Few Columns,rest Of The Columns Are Pull Based Lowest/highest Of Other Columns

i have a hive table as below with 5 columns name orderno productcategory amount description KJFSFKS 1 1 40 D1 KJFSFKS 2 2 50 D2 KJFSFKS 3 2 67 D3 KJFSFKS 4 2 10

Solution 1:

select name, orderno,  productcategory,  amount,   description 
from 
(
select name, orderno, productcategory, 
       sum(amount) over(partition by name, productcategory) amount, 
       first_value(description) over(partition by name, productcategory order by orderno desc) description,
       row_number() over (partition by name, productcategory order by orderno) rn
from  your_table
)s where rn=1; --pick lowest orderno 

---

OK
KJFSFKS 1       1       40      D1
KJFSFKS 2       2       127     D4
KJFSFKS 5       3       13      D7
KJFSFKS 8       4       8       D8
KJFSFKS 9       5       18      D10
Time taken: 12.492 seconds, Fetched: 5 row(s)

Solution 2:

select      name
           ,min(orderno)    as orderno
           ,productcategory
           ,sum(amount)     as amount
           ,max(named_struct('orderno',orderno,'description',description)).description

from        mytable

group by    name
           ,productcategory
;