Take the 2-minute tour ×

Sorry, new to oracle.

I'd like to get current and the last active record back:

ID int (PK)
RecordID int
Description varchar
TermDate datetime

Basically whenever the TermDate is null, it is active, but will get the last terminated record based off the RecordID. So RecordID is not unique, but will be unique when displayed in the results.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Assuming you want all current records, plus the last active record for each RecordID, try:

select * from
(select t.*, rank() over (partition by RecordID order by TermDate desc) rn
 from MyTable T)
where TermDate is NULL or rn = 1
share|improve this answer
Thank you! Figured Oracle would be much different than MSSQL. –  dave2118 Jan 5 '12 at 15:27
add comment

Your Answer


