I am trying to find out the highest-grossing day per location (date and gross sales amount) in my dataset. I understand that Rank ordering may help here, but im a little stuck on how to go about that. Here is the script so far:
SELECT
DateKey,
SUM(GrossSales),
LocationKey
FROM
dataset
GROUP BY
DateKey, LocationKey
ORDER BY
DateKey, LocationKey
I also tried this script too below, but it only returns the highest grossing day of all locations, not by each and every location:
SELECT
DateKey,
LocationKey,
SUM(GrossSales)
FROM
dataset
GROUP BY
DateKey, LocationKey
HAVING
SUM(GrossSales) = (SELECT MAX(GrossSales)
FROM
(SELECT SUM(GrossSales) AS GrossSales
FROM dataset
GROUP BY Datekey, LocationKey) a )
This is how is how I would like it to look like, as an example:
Is there an easy work around here, that I just cant see?
Thank you!
One approach is to perform the initial GROUP BY
calculation in a subquery and include a ROW_NUMBER() or RANK() window function to assign sequence numbers. The outer query can then filter for sequence = 1.
Something like:
SELECT
DateKey,
GrossSales,
LocationKey
FROM (
SELECT
DateKey,
SUM(GrossSales) AS GrossSales,
LocationKey,
ROW_NUMBER() OVER(PARTITION BY LocationKey ORDER BY SUM(GrossSales) DESC) AS RN
FROM
dataset
GROUP BY
DateKey, LocationKey
) A
WHERE RN = 1
ORDER BY
LocationKey
Replacing ROW_NUMBER() with RANK() would include all tied values.
See this db<>fiddle for a demo.