sql server - Rank by top customers within each separate month -

Question

I am having trouble ranking top customers by month. I created a new Rank column - but how do I break it up by month? Any help plz. Code and tables below:

The logic for ranking is selecting the top two customers per month from the tables. Also wrapped into the code (attempted at least) is renaming the date field and setting it to reflect end of month date only.

 SELECT * FROM table1;
UPDATE table1
SET DATE=EOMONTH(DATE) AS MO_END;
ALTER TABLE table1
ADD COLUMN RANK INT AFTER SALES;
UPDATE table1
SET RANK=
RANK() OVER(PARTITION BY cust ORDER BY sales DESC);
LIMIT 2

Starting wtih

------+----------+-------+--+
| CUST |   DATE   | SALES |  |
+------+----------+-------+--+
|   36 | 3-5-2018 |    50 |  |
|   37 | 3-15-18  |   100 |  |
|   38 | 3-25-18  |    65 |  |
|   37 | 4-5-18   |    95 |  |
|   39 | 4-21-18  |   500 |  |
|   40 | 4-45-18  |   199 |  |
+------+----------+-------+--+

desired end result

+------+---------+-------+------+--+
| CUST | MO_END  | SALES | RANK |  |
+------+---------+-------+------+--+
|   37 | 3-31-18 |   100 |    1 |  |
|   38 | 3-25-18 |    65 |    2 |  |
|   39 | 4-30-18 |   500 |    1 |  |
|   40 | 4-45-18 |   199 |    2 |  |
+------+---------+-------+------+--+

Answer 1

As a simple selection:

select * 
from   (
    select
          table1.*
        , DENSE_RANK() OVER(PARTITION BY cust, EOMONTH(DATE) ORDER BY sales DESC) as ranking
    from table1
    )
where ranking < 3
;

---

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

with cte as (
    select
          cust
        , DENSE_RANK() OVER(PARTITION BY cust, EOMONTH(DATE) ORDER BY sales DESC) as ranking
    from table1
    )
update cte
set sales_rank = ranking
where ranking < 3
;

There is really no reason to store the end of month, just use that function within the partition of the over() clause.

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.