how to take first value by rank or second in oracle select

I made ranking by using rank() OVER (PARTITION BY ? ORDER BY ? ASC) funtion. It is working fine. But I need to take 1st falue if max rnk of particular application_id value is 1 otherwise 2nd one.

TABLE is like this

  APPLICATION_ID    |  EMP_CODE   | DATE_ACTION   | ACTION_ID
----------------------------------------------------------------
    24744           |  110147     | 2016-09-13    | -1
    24746           |  162286     | 2016-09-12    |  0

My Select is as below:

SELECT APPLICATION_ID,EMP_CODE,DATE_ACTION,ACTION_ID,
           rank() OVER (PARTITION BY APPLICATION_ID
                  ORDER BY DATE_ACTION ASC) as rnk
FROM TABLE WHERE ACTION_ID = -1

Result is:

RNK  | APPLICATION_ID    |  EMP_CODE   | DATE_ACTION   | ACTION_ID
------------------------------------------------------------
1    | 53207             | 260829      | 2020-03-03    | -1
2    | 53207             | 161938      | 2020-03-03    | -1
3    | 53207             | 161938      | 2020-03-03    | -1
1    | 51372             | 163668      | 2020-03-04    | -1
1    | 52819             | 260829      | 2020-03-02    | -1
2    | 52819             | 161938      | 2020-03-02    | -1
3    | 52819             | 161938      | 2020-03-02    | -1
4    | 52819             | 161938      | 2020-03-02    | -1

But result should be like this:

RNK  | APPLICATION_ID    |  EMP_CODE   | DATE_ACTION   | ACTION_ID
------------------------------------------------------------
2    | 53207             | 161938      | 2020-03-03    | -1
1    | 51372             | 163668      | 2020-03-04    | -1
2    | 52819             | 161938      | 2020-03-02    | -1

Answer

You can use COUNT() analytical function also like this:

SELECT t.APPLICATION_ID, t.EMP_CODE, t.DATE_ACTION, t.ACTION_ID
FROM (
  SELECT APPLICATION_ID,EMP_CODE,DATE_ACTION,ACTION_ID,
    RANK() OVER (PARTITION BY APPLICATION_ID ORDER BY DATE_ACTION DESC) as rnk,
    COUNT(*) OVER (PARTITION BY APPLICATION_ID) counter
  FROM tablename WHERE ACTION_ID = -1
) t
WHERE t.rnk = CASE WHEN t.counter = 1 THEN 1 ELSE 2 END

Advertisement

Answer