Query to select latest Record from specific Group in SQL Server

Hi smile,

Refer the below sample Query.

SQL

DECLARE @TestDemo AS TABLE(AdmissionNo VARCHAR(10), [Month] VARCHAR(10), Recievable INT, NetBal INT, Paid INT, RemBal INT)
INSERT INTO @TestDemo VALUES('R-01','Jan',1000,1000,600,400)
INSERT INTO @TestDemo VALUES('R-01','Jan',1000,400,200,100)
INSERT INTO @TestDemo VALUES('R-02','Jan',1000,1000,400,600)
INSERT INTO @TestDemo VALUES('R-02','Jan',1000,600,300,300)
INSERT INTO @TestDemo VALUES('R-02','Jan',1000,300,200,100)
INSERT INTO @TestDemo VALUES('R-03','Jan',1000,1000,500,500)
INSERT INTO @TestDemo VALUES('R-03','Jan',1000,500,200,300)
INSERT INTO @TestDemo VALUES('R-03','Jan',1000,300,150,150)
INSERT INTO @TestDemo VALUES('R-04','Jan',1000,1000,700,300)
INSERT INTO @TestDemo VALUES('R-05','Jan',1000,1000,800,200)

;WITH Employee_CTE (RowNo,AdmissionNo,Month,Recievable,NetBal, Paid,RemBal)
AS
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,AdmissionNo,Month,Recievable,NetBal,Paid,RemBal 
FROM @TestDemo)

SELECT AdmissionNo,Month,Recievable,NetBal,Paid,RemBal FROM Employee_CTE WHERE RowNo IN (SELECT MAX(RowNo) FROM Employee_CTE GROUP BY AdmissionNo)

Output