Query to get the max, min and max-1 in a single query

See more:

SQL

Am trying to query a table to get max,min and max-1 values based on IDs.Here i need to get the details of all projects from the subquery 'b' but am getting null values for most of the columns -A.SCAN_ID AS PRIOR_SCAN which is not supposed to be.

What I have tried:

select a.PROJECT,
a.SOLUTION,
a.first_scan,
a.last_scan,
b.PRIOR_SCAN
from
(
SELECT PROJECT,SOLUTION,MIN(SCAN_ID)first_scan, MAX(SCAN_ID)last_scan
FROM PORTAL_CSA.RPT_Scan_Summary
GROUP BY PROJECT,SOLUTION)a
LEFT OUTER JOIN
(
SELECT A.SOA_SECTOR, A.PROJECT,A.SOLUTION, A.SCAN_ID AS PRIOR_SCAN
FROM
(SELECT DISTINCT SOA_SECTOR, PROJECT,SOLUTION, SCAN_ID,
DENSE_RANK() OVER (PARTITION BY PROJECT,SOLUTION ORDER BY SCAN_ID DESC) AS PRIOR_SCAN_ID FROM PORTAL_CSA.RPT_Scan_Summary) A
WHERE A.PRIOR_SCAN_ID = 2 )b ON b.PROJECT =a.PROJECT AND b.SOLUTION =a.SOLUTION