-1

I've tried to achieve this but I'm still having multiple identical items in column item_name even using DISTINCT.

this is what I got

contract_id | item_name | max(amount) | 
1           | item1     | 20
12          | item1     | 25
111         | item1     | 10
3           | item2     | 15
32          | item2     | 30

but this is what I want

contract_id | item_name | max(amount) | 
12          | item1     | 25
32          | item2     | 30



SELECT DISTINCT tb2.item_detail_id
       tb2.item_name
       tb1.contract_id
       max(tb1.amount)

FROM main_table                                              tb1

INNER JOIN (SELECT DISTINCT t_tb2.item_detail_id
            MAX(t_tb1.amount)
            FROM main_table                                  t_tb1
            INNER JOIN item                                  t_tb2
             ON t_tb1.item_detail_id = t_tb2.item_detail_id
            GROUP BY t_tb2.item_detail_id)                   temp
 ON tb1.item_detail_id = temp.item_detail_id
INNER JOIN item                                              tb2
 ON tb1.item_detail_id = tb2.item_detail_id

GROUP BY tb2.item_name,
        tb1.contract_id,
        tb1.amount
MT0
  • 143,790
  • 11
  • 59
  • 117
Newbie
  • 1
  • 2
  • Please, describe how to determine the result you want in general words. How to decide what `contract_id` should be returned? If you `group by` something, then this columns (expressions) determine the context and you do not need to aggregate them – astentx Sep 03 '21 at 17:25
  • The contract_id with the maximum amount of each product especially when the same product has several amounts. – Newbie Sep 04 '21 at 15:08

1 Answers1

0

You can use aggregation, but like this:

SELECT tb2.item_name
       MAX(tb1.contract_id) KEEP (DENSE_RANK FIRST ORDER BY tb1.amount DESC) as contract_id,
       MAX(tb1.amount)

FROM . . .
GROUP BY tb2.item_name
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786