How to choose the most recent record of the sql array among several records?

advertisements

I have developed a website which facilitates a LAWYER to enter CASE DETAILS along with NEXT HEARING DATES. Each case can have more than 1 HEARING DATES. Let's assume we have 3 CASES in table with each having multiple hearing dates but I want to pick most last HEARING DATE of each case then how I should go for it ?

I have used TOP then MAX with select statement but nothing worked.

What I am stroing are:

pk_Cases_CaseID
CaseNo
CaseTitle
CaseRemarks
CaseNextHearingDate
IsCaseFinalized
CaseEntryDateTime


In SQL Server, the typical method would be to use row_number(). Your question doesn't really explain what the data layout is (the column names seem quite different from the description). However, the logic looks like:

select h.*
from (select h.*,
             row_number() over (partition by caseno order by hearingdate desc) as seqnum
      from hearings h
     ) h
where seqnum = 1;