CODEWITHSUNDEEP
sqlsql-servergroup-bymax
Steven Snyder
Steven Snyder

Reputation: 21

How to select "best" (Max) value by day for each category? In MS SQL

I'm using MS SQL and am trying to find the "best" (Max sales volume) day for customer reps. I currently have a table with the columns:

RepName, Date, Volume

That has the following data

 - Joe Salesman,    01/01/2021, 2
 - Mike Moneymaker, 01/01/2021, 14
 - Rachel Revenue, 01/01/2021, 7
 - Joe Salesman,    01/02/2021, 9
 - Mike Moneymaker, 01/02/2021, 8
 - Rachel Revenue, 01/02/2021, 12
 - Joe Salesman,    01/03/2021, 7
 - Mike Moneymaker, 01/03/2021, 11
 - Rachel Revenue, 01/03/2021, 14

with this continuing up to today's data.

I'm trying to list the Max Volume per Rep, and the respective date the rep obtained that volume. This would look like:

- Joe Salesman, 01/02/2021, 9  
- Mike Moneymaker, 01/01/2021, 14
- Rachel Revenue, 01/03/2021, 14

Current Code I'm using is more or less in this format:

SELECT RepName, Date, MAX(Volume)
FROM MyTable
GROUP BY RepName, Date

However, this is returning all rows of data in MyTable, instead of showing the maximums.

Anybody have any insight or tips?

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use window functions:

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY RepName ORDER BY Volume DESC) as seqnum
      FROM MyTable t
     ) t
WHERE seqnum = 1;

Upvotes: 1

Related Questions