CODEWITHSUNDEEP
sqloracle-database
saru
saru

Reputation: 23

Query to find first,last and prior to last values

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

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)  
);

Sample records

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);

What i tried

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;

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Is this what you want?

select t.*
from  (Select t.*
              row_number() over (partition by project order by scan_id) as seqnum,
              count(*) over (partition by project) as cnt
       from portal_CSA.table1 t
      ) t
where seqnum in (1, cnt - 1, cnt);

If you want this in one row, you can use conditional aggregation:

select project,
       max(case when seqnum = 1 then scan_id end) as first_scan_id,
       max(case when seqnum = cnt - 1 and cnt > 2 then scan_id end) as prior_scan_id,
       max(case when seqnum = cnt and cnt > 1 then scan_id end) as last_scan_id,
from  (Select t.*
              row_number() over (partition by project order by scan_id) as seqnum,
              count(*) over (partition by project) as cnt
       from portal_CSA.table1 t
      ) t
where seqnum in (1, cnt - 1, cnt)
group by project;

Upvotes: 0

Related Questions