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