to get a unique record month wise using row number in sql server-CodePudding

I have a record where , I want to find unique record month wise on basis on max createddate the code which I am using is

 row_number()over(partition by mobile, createddate order by column1, column2, column3)

but using these I am getting multiple record.

I want one record per month base on max createddate

Expected Input

    MOBILENO    CTREATEDDATE    BRESTATUS
 0  mobile1     2022-07-10      False
 1  mobile1     2022-07-14      True
 2  mobile2     2022-07-14      True
 3  mobile2     2022-08-15      True

expected output , since mobile2 is present in both the month, and mobile1 present only in July' month

 1  mobile1     2022-07-14      True
 2  mobile2     2022-07-14      True
 3  mobile2     2022-08-15      True

CodePudding user response：

You need to partition the row number by MOBILENO ,YEAR(CTREATEDDATE), MONTH(CTREATEDDATE), try the following:

SELECT T.ID, T.MOBILENO, T.CTREATEDDATE
  FROM
  (
    SELECT ID, MOBILENO, CTREATEDDATE,
       ROW_NUMBER() OVER (PARTITION BY MOBILENO, YEAR(CTREATEDDATE), MONTH(CTREATEDDATE) ORDER BY CTREATEDDATE DESC) RN
    FROM table_name
  ) T
WHERE T.RN=1
ORDER BY T.MOBILENO

See a demo.