title: Query to find first,last and prior to last values
body: <p>I have a requirement to get the first,last and prior to last id from a table(max,min,max-1) to analyze the ID details of each and every project which are scanned first,last and prior to last.If there is only one scan,then i need to capture that alone.But if we use Max/min or Rownum then it will capture the same value for both max and min values if we have only one id alone.
  Source table</p>
  
  <pre><code>CREATE TABLE TABLE1  
  (  
  SOA_SECTOR VARCHAR2(128),  
  SOA_REGION VARCHAR2(128),  
  USERNAME VARCHAR2(128),  
  PROJECT VARCHAR2(256) NOT NULL,  
  SOLUTION VARCHAR2(256),  
  PATH VARCHAR2(2048) NOT NULL,  
  TIME DATE NOT NULL,  
  LOC NUMBER(10) NOT NULL,  
  SCAN_ID NUMBER(10),  
  SESSION_ID VARCHAR2(256),  
  FILE_ID VARCHAR2(256),  
  FILE_INFO_ID VARCHAR2(256),  
  SCAN_YEAR_MON VARCHAR2(7),  
  SCAN_YEAR VARCHAR2(7),  
  SCAN_MONTH VARCHAR2(7),  
  LOAD_DATE_TIME DATE NOT NULL,  
  CONSTRAINT rpt_scan_summary_pk PRIMARY KEY (SCAN_ID, FILE_ID)  
  );  
  target table  
  CREATE TABLE table2 (  
  PROJECT VARCHAR2(256 CHAR) NOT NULL,  
  SOLUTION VARCHAR2(256 CHAR),  
  FIRST_SCAN_ID NUMBER(10) NOT NULL,  
  LAST_SCAN_ID NUMBER(10) NOT NULL,  
  PRIOR_SCAN_ID NUMBER(10) NOT NULL,  
  CONSTRAINT pk_tmp_first_last_scan_ids_id PRIMARY KEY (PROJECT)  
  );  
  </code></pre>
  
  <p>Sample records</p>
  
  <pre><code>Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('park.Cash.Trading.csproj','TWSTicketing.sln',2491149,2514275,2514274);  
  Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('RBCheck.Services.ArchiveAndPurgeService.csproj','RB.Citi.GlobalCheck.sln',1989546,5385142,5384987);  
  Insert into EXPORT_TABLE (PROJECT,SOLUTION,FIRST_SCAN,LAST_SCAN,PRIOR_SCAN) values ('Cmb.UI.Framework.Core.csproj','BE2UI.sln',51839,61995,61994);  
  </code></pre>
  
  <p>What i tried</p>
  
  <pre><code>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; 
  </code></pre>