SQL to skip max row on condition

Question

I need to return latest available row but on conditions. Since Hive doesn't support PL-T/SQL I need to work on functions.

Current code selects latest record only and doesn't take ACTIVE_F into consideration.

WITH CTE AS 

(select 
ID, 
myuser_insert_time as insert_time,
max(myuser_insert_time) OVER (PARTITION BY ID ORDER BY ID) as rn
from tbl1)

SELECT * FROM CTE 
WHERE rn =  insert_time

My data:

MYUSER_INSERT_TIME        ACTIVE_F
2019-06-14 15:00:32.000   6
2019-03-06 15:54:22.000   0
2019-01-25 08:43:45.000   1
2018-12-13 09:49:50.000   0
2018-11-24 10:11:06.000   0
2018-11-06 12:17:34.000   1
2018-07-04 16:59:15.000   0
2018-05-29 12:22:15.000   1
2018-05-24 20:19:00.000   2
2018-05-24 20:19:00.000   2

Expected behaviour:

1. Find latest record (done)

2. Check ACTIVE_F (When 6 - move to the next row and return that row, else proceed to next row)

Desired result:

MYUSER_INSERT_TIME        ACTIVE_F
2019-03-06 15:54:22.000   0

Answer 1

Order rows conditionally by ACTIVE_F so 6's go after all other values

WITH CTE AS 

(select 
ID, 
myuser_insert_time as insert_time,
row_number() OVER (PARTITION BY ID ORDER BY case ACTIVE_F when 6 then 1 else 0 end,  eendmyuser_insert_time desc) as rn
from tbl1)

SELECT * FROM CTE 
WHERE rn = 1