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 previous 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
 
Read Message
Read Message
Read Message
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: Mon Jan 20 14:37:09 CST 2020