CODEWITHSUNDEEP
sqlhadoophivemaxhiveql
marcin2x4
marcin2x4

Reputation: 1429

SQL to skip max row on condition

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

Upvotes: 3

Views: 325

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You seem to just want to filter out active_f = 6, if I understand correctly. You should do this before calculating the max insert time; that is, in the CTE:

with cte as (
      select ID, myuser_insert_time as insert_time,
             max(myuser_insert_time) over (partition by ID) as max_myuser_insert_time
      from tbl1
      where active_f <> 6
    )
select * 
from CTE 
where myuser_insert_time = insert_time;

Your max() also had an order by column, so you were taking a cumulative max(). This is not necessary. The order by is unnecessary (even if the code worked).

EDIT:

If you need "6" if it is the only row, then use row_number() and make it the last row:

with cte as (
      select ID, myuser_insert_time as insert_time,
             row_number() over (partition by id
                                order by (case when active_f = 6 then 2 else 1 end),
                                         myuser_insert_time desc
                               ) as rn
      from tbl1
    )
select * 
from CTE 
where rn = 1;

Upvotes: 1

leftjoin
leftjoin

Reputation: 38290

Add case when active_f =6 then 1 else 0 end to the order by clause of the analytic function. Records with active_f !=6 will be preferred. Also use row_number and order by myuser_insert_time desc:

WITH CTE AS 

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

SELECT * FROM CTE 

WHERE rn =  1

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 2

Related Questions