So I have data look like in Picture. Column name Track is to show the step of the state. Column name Current_state is the status of the app right now. Column name Current_state_hist is the history of the status.

So right now the current status now is AP. I want to Select all the status before the last status right now (AP in Track 13 & 14) without remove the status AP in track 5 - 8.

Can somebody help me for this case? Thank you Example of the data

CodePudding user response：

You can use EXISTS for that:

Schema and insert statements:

 create table table1(id int, track int, current_state varchar(10), current_state_hist varchar(10), total_unit int);


 insert into table1 values(1,1,'AP','OD',1)
 insert into table1 values(1,2,'AP','OD',1)
 insert into table1 values(1,3,'AP','OD',1)
 insert into table1 values(1,4,'AP','OD',1)
 insert into table1 values(1,5,'AP','AP',1)
 insert into table1 values(1,6,'AP','AP',1)
 insert into table1 values(1,7,'AP','AP',1)
 insert into table1 values(1,8,'AP','AP',1)
 insert into table1 values(1,9,'AP','OD',1)
 insert into table1 values(1,10,'AP','OD',1)
 insert into table1 values(1,11,'AP','OD',1)
 insert into table1 values(1,12,'AP','OD',1)
 insert into table1 values(1,13,'AP','AP',1)
 insert into table1 values(1,14,'AP','AP',1)

Query:

 SELECT ID,TRACK,CURRENT_STATE,CURRENT_STATE_HIST,TOTAL_UNIT
 FROM TABLE1  T1 
 WHERE EXISTS
            (
                SELECT 1 FROM TABLE1 T2 
                WHERE CURRENT_STATE<>CURRENT_STATE_HIST 
                AND T1.TRACK<=T2.TRACK
            )

Output:

db<>fiddle here

CodePudding user response：

You can find the latest status without having to query the table twice using the ROW_NUMBER analytic function:

SELECT id, track, current_state, current_state_hist, total_unit
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY track DESC)
           - ROW_NUMBER() OVER (
               PARTITION BY current_state_hist ORDER BY track DESC
             ) AS rn
  FROM   table_name t
)
WHERE rn > 0;

Or, from Oracle 12:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY track DESC
  ALL ROWS PER MATCH
  PATTERN ( ^ {- same_hist  -} any_hist* )
  DEFINE
    same_hist AS FIRST(current_state_hist) = current_state_hist
)

Which, for the sample data:

CREATE TABLE table_name (id, track, current_state, current_state_hist, total_unit) AS
SELECT 1,  1, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1,  2, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1,  3, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1,  4, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1,  5, 'AP', 'AP', 1 FROM DUAL UNION ALL
SELECT 1,  6, 'AP', 'AP', 1 FROM DUAL UNION ALL
SELECT 1,  7, 'AP', 'AP', 1 FROM DUAL UNION ALL
SELECT 1,  8, 'AP', 'AP', 1 FROM DUAL UNION ALL
SELECT 1,  9, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1, 10, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1, 11, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1, 12, 'AP', 'OD', 1 FROM DUAL UNION ALL
SELECT 1, 13, 'AP', 'AP', 1 FROM DUAL UNION ALL
SELECT 1, 14, 'AP', 'AP', 1 FROM DUAL;

Both output:

ID	TRACK	CURRENT_STATE	CURRENT_STATE_HIST	TOTAL_UNIT
1	12	AP	OD	1
1	11	AP	OD	1
1	10	AP	OD	1
1	9	AP	OD	1
1	8	AP	AP	1
1	7	AP	AP	1
1	6	AP	AP	1
1	5	AP	AP	1
1	4	AP	OD	1
1	3	AP	OD	1
1	2	AP	OD	1
1	1	AP	OD	1

I want the ouput is to select all except the last 2 row... because it's current status...

If you just want to ignore the last 2 rows then: order the rows, then assign a ROWNUM pseudo-column to the ordered rows, then filter on the ROWNUM to exclude the latest two rows:

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn
  FROM   (
    SELECT *
    FROM   table_name
    ORDER BY track DESC
  ) t
)
WHERE  rn >= 3;

Or, using the ROW_NUMBER analytic function:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMMBER() OVER (ORDER BY track DESC) AS rn
  FROM   table_name t
)
WHERE  rn >= 3;

Or, from Oracle 12:

SELECT *
FROM   table_name
ORDER BY track DESC
OFFSET 2 ROWS
FETCH FIRST 100 PERCENT ONLY;

db<>fiddle here