Home » RDBMS Server » Performance Tuning » Sql query to get Max,min and max-1 row from a table (Oracle 11.2)
Sql query to get Max,min and max-1 row from a table [message #670706] Sun, 22 July 2018 01:11 Go to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi - 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)
);

here from source table scan_id is our source feed for first/last/prior scan_ids
Re: Sql query to get Max,min and max-1 row from a table [message #670707 is a reply to message #670706] Sun, 22 July 2018 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Sun, 24 June 2018 01:01
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Sql query to get Max,min and max-1 row from a table [message #670708 is a reply to message #670707] Sun, 22 July 2018 09:36 Go to previous message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi Michel - I believe you are looking for 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;

This code returns the same value for max and min value if we have only one scan_id



--Moderator edit: added the [code] tags, please do so yourself in future.

[Updated on: Sun, 22 July 2018 11:38] by Moderator

Report message to a moderator

Previous Topic: please help for sql query to find out total number of concurrent connections in database
Next Topic: performance tuning
Goto Forum:
  


Current Time: Wed Dec 11 00:30:23 CST 2019