CODEWITHSUNDEEP
oracle-databaseexists
Anonymous
Anonymous

Reputation: 297

find nearest row of different type in oracle

My table looks like

__   Key   type  timeStamp             flag
1 )    1     B    2015-06-28 22:19:26   Y
2 )    1     B    2015-06-28 22:20:22   Y
3 )    1     C    2015-06-28 22:22:06   N
4 )    1     A    2015-06-28 22:25:11   N
5 )    1     B    2015-06-28 22:29:44   Y
6 )    1     A    2015-06-28 22:33:33   N
7 )    1     B    2015-06-28 22:35:21   N
8 )    1     B    2015-06-28 22:39:34   Y
9 )    1     B    2015-06-28 22:43:53   N
10)    1     A    2015-06-28 22:45:53   N

I need to find out all the types of A whose flag='N' with respect to which there exist type B whose timestampOF(B)<timestampOF(A) and Flag(B)='Y' and key(A)=key(B).

note: If there exist two B previous than A than take the B with max timestamp.(ROW[8,9,10] 9 is taken instead of 8)

OUTPUT

__   Key   type  timeStamp             flag
4 )    1     A    2015-06-28 22:25:11   N
6 )    1     A    2015-06-28 22:33:33   N

My approach 

SELECT  *
FROM    tab TAB_OUT
WHERE   TAB_OUT.TYPE='A'
    AND TAB_OUT.FLAG='N'
    AND EXISTS(
            SELECT   *
            FROM    tab TAB_IN
            WHERE   TAB_IN.KEY = TAB_OUT.KEY
                    AND     TAB_IN.TYPE='B'
                    AND     TAB_OUT.FLAG='Y'
                    AND     TAB_IN.timestamp<TAB_OUT.timestamp
                    AND     TAB_IN.timestamp = (SELECT MAX(timestamp) from       
 tab where timestamp< `TAB_OUT.timestamp`)
 );

  1. But in this i can not use TAB_OUT.timestamp in third level query. Is there any alternative solution to solve this problem.

  2. In my query note: part is not satisfied as my query as it skips no. 9) and satisfy condition with no. 8)

Upvotes: 1

Views: 118

Answers (2)

MT0
MT0

Reputation: 168578

A solution that only requires a single table scan:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Key, type, timeStamp, flag ) AS
          SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:19:26' AS DATE ), 'Y' FROM DUAL
UNION ALL SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:20:22' AS DATE ), 'Y' FROM DUAL
UNION ALL SELECT 1, 'C', CAST( TIMESTAMP '2015-06-28 22:22:06' AS DATE ), 'N' FROM DUAL
UNION ALL SELECT 1, 'A', CAST( TIMESTAMP '2015-06-28 22:25:11' AS DATE ), 'N' FROM DUAL
UNION ALL SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:29:44' AS DATE ), 'Y' FROM DUAL
UNION ALL SELECT 1, 'A', CAST( TIMESTAMP '2015-06-28 22:33:33' AS DATE ), 'N' FROM DUAL
UNION ALL SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:35:21' AS DATE ), 'N' FROM DUAL
UNION ALL SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:39:34' AS DATE ), 'Y' FROM DUAL
UNION ALL SELECT 1, 'B', CAST( TIMESTAMP '2015-06-28 22:43:53' AS DATE ), 'N' FROM DUAL
UNION ALL SELECT 1, 'A', CAST( TIMESTAMP '2015-06-28 22:45:53' AS DATE ), 'N' FROM DUAL

Query 1:

SELECT  Key,
        type,
        timeStamp,
        flag
FROM (
  SELECT  Key,
          type,
          timeStamp,
          flag,
          LAG( CASE WHEN type = 'B' THEN flag END ) IGNORE NULLS OVER ( PARTITION BY Key ORDER BY timeStamp ) AS prev_b_flag
  FROM    table_name t
  WHERE   type IN ( 'A', 'B' )
)
WHERE   type        = 'A'
AND     flag        = 'N'
AND     prev_b_flag = 'Y'

Results:

| KEY | TYPE |              TIMESTAMP | FLAG |
|-----|------|------------------------|------|
|   1 |    A | June, 28 2015 22:25:11 |    N |
|   1 |    A | June, 28 2015 22:33:33 |    N |

Upvotes: 1

Husqvik
Husqvik

Reputation: 5809

SELECT
    *
FROM
    tab A
WHERE
    flag = 'N' AND type = 'A'
    AND EXISTS (
        SELECT
            NULL
        FROM
            tab B
        WHERE
            type = 'B'
            AND A.timestamp > timestamp AND A.Key = Key
        GROUP BY
            Key
        HAVING
            MAX(flag) KEEP (DENSE_RANK LAST ORDER BY timestamp) = 'Y'
    );

There is no need to make correlated query to select flag from the the last record. Using aggregate KEEP clause is more efficient way. In this case it sort the groups by timestamp and keeps only the last value for the aggregation (last timestamp you wanted), so there comes only single record to the MAX function and we just take the FLAG value from it.

Here is simple example:

WITH sample (value1, value2) AS (
    SELECT 1, 'Y' FROM DUAL UNION ALL
    SELECT 2, 'X' FROM DUAL
)
SELECT
    MIN(value2) KEEP (DENSE_RANK LAST ORDER BY value1) value2
FROM
    sample

This returns value2 from the record with highest value1.

Upvotes: 1

Related Questions