Fetching Top 25 Rows per Column 1's records

Question

My query is as follows:

SELECT 
    ACCOUNT_NAME, BRAND, 
    SUM(SALES_YTD) AS YTD, 
    SUM(SALES_YTD_YA) AS YTD_YA
FROM
    ACCOUNTS
WHERE 
    CHAIN_NAME = 'LIQUOR_DEPOT'
GROUP BY 
    ACCOUNT_NAME, BRAND
ORDER BY 
    ACCOUNT_NAME, SUM(SALES_YTD) DESC
FETCH FIRST 25 ROWS ONLY

There are 6 stores under the name Liquor Depot, so I want it to return the top 25 brands for each of the 6 stores in one query instead of having to write another WHERE condition for each store. Is this possible? If so how do I write the query?

Thanks!

Answer 1

Try the following

SELECT * FROM (
    SELECT
         ACCOUNT_NAME, BRAND, 
         SUM(SALES_YTD) AS YTD, 
         SUM(SALES_YTD_YA) AS YTD_YA
         RANK () OVER ( 
            PARTITION BY brand_id
            ORDER BY SUM(SALES_YTD) DESC        
          ) rank
    FROM
        ACCOUNTS
) t
WHERE rank <26;