Ranking Within Multiple Groups & Efficient Query For Multiple Table Updates

I'm trying to add rank by sales by month and also change the date column to a 'month end' field that would show only last day of month. Can i do two sets in a row like that without

Solution 1:

I do not know why you want EOMONTH as a stored value, but what you have for that will work.

I would not use [rank] as a column name as I avoid any words that are used in SQL, maybe [sales_rank] or similar.

ALTERTABLE table1
ADDCOLUMN [sales_rank] INT AFTER sales;

with cte as (
    select
          cust
        , DENSE_RANK() OVER(PARTITIONBY cust ORDERBY sales DESC) as ranking
    from table1
    )
update cte
set sales_rank = ranking
where ranking <3
;

LIMIT 2 is not something that can be used in SQL Server by the way, and it sure can't be used "per grouping". When you use a "window function" such as rank() or dense_rank() you can use the output of those in the where clause of the next "layer". i.e. use those functions in a subquery (or cte) and then use a where clause to filter rows by the calculated values.

Also note I used dense_rank() to guarantee that no rank numbers are skipped, so that the subsequent where clause will be effective.