Query to select latest Record from specific Group in SQL Server

Last Reply 7 months ago By dharmendr

Posted 7 months ago

I want to display the record of Sr. No. 2,5,8,9,10 from the given table. How to write the query to select the record of last specific AdmissionNo? 

AdmissionNo

Month

Recievable

NetBal

Paid

RemBal

Sr.No

R-01

Jan

1000

1000

600

400

1

R-01

Jan

1000

400

200

100

2

R-02

Jan

1000

1000

400

600

3

R-02

Jan

1000

600

300

300

4

R-02

Jan

1000

300

200

100

5

R-03

Jan

1000

1000

500

500

6

R-03

Jan

1000

500

200

300

7

R-03

Jan

1000

300

150

150

8

R-04

Jan

1000

1000

700

300

9

R-05

Jan

1000

1000

800

200

10

in the above table R-02 is repeating three times. It may be repeat five times or even one time while paying fees.

Posted 7 months ago Modified on 7 months ago

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

AdmissionNo Month Recievable NetBal Paid RemBal
R-01 Jan 1000 400 200 100
R-02 Jan 1000 300 200 100
R-03 Jan 1000 300 150 150
R-04 Jan 1000 1000 700 300
R-05 Jan 1000 1000 800 200