-2

I am trying to get the highest spend and show the vendor that has the highest spend in the category . Here is what i have: 

Vendor    Category   Spend
3M         cat1       200
XX         cat6       150
Bos        cat1       700
JJ         cat0       500
KLM        cat0       100       
XX         cat0       400

This is the desired results:

Vendor    Category   Spend
XX         cat6       150
Bos        cat1       700
JJ         cat0       500

i am not getting the right results

select vendor, category, sum(spend) ,row_number() over(partition by vendor order by spend desc) as roworder
from myTable
group by vendor, category
moe
  • 5,149
  • 38
  • 130
  • 197
  • This has been asked so many times. Gordon has a literally identical answer on one of those that has been highlighted as a duplicate. – Thom A Oct 23 '19 at 17:29

1 Answers1

1

Use a correlated subquery to filter the data:

select t.*
from myTable t
where t.spend = (select max(t2.spend)
                 from myTable t2
                 where t2.category = t.category
                );

You can also use window functions:

select t.*
from (select t.*,
             row_number() over (partition by category order by spend desc) as seqnum
      from myTable t
     ) t
where seqnum = 1;

Use rank() if you want ties in the event of duplicate max values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, i need to sum the spend because sometimes there are multiple vendor and categories with the same vendor names and categories but different spend so i need to always sum the spend – moe Oct 23 '19 at 17:36
  • You don't "SUM" `spend` in your expected results, @moe . The value is `700` for `cat1` for example, not `900`. – Thom A Oct 23 '19 at 17:46
  • this was just a sample data but in my real data i have where vendor and category are the same but with different spend. sorry i was trying simplify the sample data – moe Oct 23 '19 at 17:47
  • 1
    If your sample data isn't representative of the problem @moe then you aren't going to get representative answers. I suggest posting a new question (possibly deleting this one) with a more informative sample set and results. – Thom A Oct 23 '19 at 17:49