reggie86 reggie86 - 3 months ago 8
SQL Question

Find 2nd Most Recent Date SQL with JOINS

I'm trying to return the second most recent date for a set of projects in SQL, but I'm getting the error 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
Here is my code:

SELECT ProjectId, max(CreatedDateTime)
FROM NTNote.dbo.nProjectReference P
INNER JOIN NTNote.dbo.nJournal J
ON J.JournalID = P.JournalID
Where CreatedDateTime < (SELECT max(CreatedDateTime), projectid
FROM NTNote.dbo.nProjectReference P
INNER JOIN NTNote.dbo.nJournal J
ON J.JournalID = P.JournalID
Group by ProjectId)
Group by ProjectId


Any help would be greatly appreciated!

Answer

use the below code for desired output ( sql server).

with cte_grp
as
 (SELECT ProjectId,CreatedDateTime,ROW_NUMBER () OVER(PARTITION BY ProjectId ORDER BY CreatedDateTime desc) RNo
     FROM NTNote.dbo.nProjectReference P                                    
     INNER JOIN NTNote.dbo.nJournal J                                   
        ON J.JournalID = P.JournalID)

        SELECT *
        FROM cte_grp
        WHERE RNO=2