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.