Hi irshad.niet,
Refer below query. Specify multiple columns in the PARTITION BY.
SQL
CREATE TABLE #Demo
(
Branchid INTEGER NOT NULL,
Year DATE NOT NULL
)
INSERT INTO #Demo(Branchid,Year) VALUES(1,'2021-09-01')
INSERT INTO #Demo(Branchid,Year) VALUES(1,'2021-09-01')
INSERT INTO #Demo(Branchid,Year) VALUES(1,'2022-10-01')
INSERT INTO #Demo(Branchid,Year) VALUES(2,'2021-09-01')
INSERT INTO #Demo(Branchid,Year) VALUES(2,'2022-10-01')
SELECT ROW_NUMBER() OVER
(
PARTITION BY Branchid, Year
ORDER BY Year DESC
) AS SrNo
,BranchId
,Year
FROM #Demo
DROP TABLE #Demo
Output
| SrNo |
BranchId |
Year |
| 1 |
1 |
9/1/2021 |
| 2 |
1 |
9/1/2021 |
| 1 |
1 |
10/1/2022 |
| 1 |
2 |
9/1/2021 |
| 1 |
2 |
10/1/2022 |
