sql-servert-sqlrankingdense-rank

T-SQL/SMSS - Highest Value in Date-Range by Location


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:

enter image description here

Is there an easy work around here, that I just cant see?

Thank you!


Solution

  • 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.