Get highest value using partition in SQL [duplicate]

Question

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

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
– Thom A ♦, Commented Oct 23, 2019 at 17:29

Gordon Linoff · Accepted Answer · 2019-10-23 17:26:10Z

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.

answered Oct 23, 2019 at 17:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

moe Over a year ago

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

Thom A Over a year ago

You don't "SUM" spend in your expected results, @moe . The value is 700 for cat1 for example, not 900.

moe Over a year ago

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

Thom A Over a year ago

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.

Collectives™ on Stack Overflow

Get highest value using partition in SQL [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related