CREATE OR REPLACE PROCEDURE PROC1 AS
BEGIN
INSERT INTO table2
SELECT
        first_scan.PROJECT,
        first_scan.SOLUTION,
        first_scan.First_Scan_ID,
        last_scan.Last_Scan_ID,
       prior_scan.Prior_Scan_ID
      FROM (
             Select PROJECT,
               SOLUTION,First_Scan_ID,seq from (SELECT
               PROJECT,
               SOLUTION,
               SCAN_ID First_Scan_ID,
               ROW_NUMBER()
               OVER (
                 PARTITION BY PROJECT
                 ORDER BY SCAN_ID ASC ) seq
             FROM portal_CSA.table1)
             where Seq=1
             
           ) first_scan
        JOIN (
               Select PROJECT,
               SOLUTION,Last_Scan_ID,seq from (SELECT
               PROJECT,
               SOLUTION,
               SCAN_ID Last_Scan_ID,
               ROW_NUMBER()
               OVER (
                 PARTITION BY PROJECT
                 ORDER BY SCAN_ID Desc ) seq
             FROM portal_CSA.table1)
             where Seq=1
             ) last_scan ON first_scan.PROJECT = last_scan.PROJECT -- AND first_scan.SOLUTION = last_scan.SOLUTION
       left outer JOIN (
                Select PROJECT,
               SOLUTION,Prior_Scan_ID,seq from (SELECT
               PROJECT,
               SOLUTION,
               SCAN_ID Prior_Scan_ID,
               ROW_NUMBER()
               OVER (
                 PARTITION BY PROJECT
                 ORDER BY SCAN_ID Desc ) seq
             FROM portal_CSA.table1)
             where Seq=2
             ) prior_scan ON  first_scan.PROJECT=prior_scan.PROJECT;
END;