CODEWITHSUNDEEP
sqlsql-server-2008
nichu09
nichu09

Reputation: 892

Query for Last Fees Date in sql server 2008

I am beginner in sql server.i have two table, TestshriMaster and Testshrifees. i need one record which have student last paid fees details. i wrote one query ,but is there any simple way to do this. help appreciated.3 user in table which are "nixon","shri","nixon".

enter image description here

Upvotes: 1

Views: 40

Answers (1)

dnoeth
dnoeth

Reputation: 60472

SQL Server supports Standard SQL's Windowed Aggregate Functions, in your case you need a ROW_NUMBER:

select ... 
from TestshriMaster as sm
 (
   select *, 
      ROW_NUMBER()                         -- apply a ranking 
      OVER (PARTITION BY studentid         -- for each student
            ORDER BY feesdate DESC ) AS rn -- based on descending dates
   from Testshrifees
 ) as sf
on sm.studentid = sf.stundentid
where sf.rn = 1                            -- return only the latest row

Upvotes: 1

Related Questions