We help IT Professionals succeed at work.

Get Top 20 by value per name

Hello Experts,

How do I get the top 20 by Charges for each Store Name ?

I know this is just easy but I cannot figure it out.

See example attached.

Thank you for all your help.
GetTheTop20ChargesByStoreName.xlsx
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT StoreName, Charges, Payments, Adjustments, AccountsReceivable
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY StoreName ORDER BY Charges DESC) AS row_num
    FROM dbo.your_table_name
) AS query1
WHERE row_num <= 20 /*CORRECTION from earlier code, which had row_num = 1*/
ORDER BY StoreName, Charges DESC


Author

Commented:
@Scott,

Thank you for your help.

Your query is perfectly correct but I asked the wrong question.

Is there a way I can reverse if Top 20 by Store Name on Charges?

Show only top 20 Store Name. I know my example has only 4 store name.


Thank you.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I think this is what you mean:

SELECT StoreName, Charges, Payments, Adjustments, AccountsReceivable
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY Charges DESC) AS row_num
    FROM dbo.your_table_name
) AS query1
WHERE row_num <= 20
ORDER BY Charges DESC, StoreName


Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Notice too that I had to make a correction to my other code, maybe that would give you the actual solution you need too.

Author

Commented:
Sorry about that.

Author

Commented:
@Scott:
Thank you for all your help.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
No worries, glad it helped.