Issue

This Content is from Stack Overflow. Question asked by qaiser

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

Solution

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.

This Question was asked in StackOverflow by qaiser and Answered by Ahmed It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.