SQL Add 2nd Most Recent Date From Another Table

[cleteh]

SELECT A.FILE_NO, DATE() - 1 AS Yesterday, A.BA_INT_RATE, (SELECT MAX(sys.SystemDate) FROM SystemDate AS sys) AS systemDate
FROM Claim AS A
WHERE A.STATUS_CODE = "OPN";

The part of the code above that's red selects the most recent record from the system date table.
I need to add a new field which displays the 2nd most recent date from the table.

There are no duplicate dates but the dates do skip around so I can't simply use (systemDate - 1) AS lastSystemDate

[Bob Phillips]

You gonna like this, not a lot


SELECT A.FILE_NO
,DATE() - 1 AS Yesterday
,A.BA_INT_RATE
,(SELECT MAX(SystemDate) FROM SystemDate) AS systemDate
,(SELECT MAX(SystemDate) FROM SystemDate WHERE SystemDate < (SELECT MAX(SystemDate) FROM SystemDate)) AS systemDate1
FROM Claim AS A
WHERE A.STATUS_CODE = "OPN";