Filter data based on group SQL Netezza

Please I have the below table, I need to filter the data based on the DATE column, In the Sus_ID level if the DATE column has NULL get this row or if the hasn’t NULL record will get the row for the newest DATE

|Sub_ID |Cust_ID    |C_Date    |
|-------+-----------+----------|
|123456 |2233       |2021-02-21|
|123456 |2211       |2021-01-21|
|123456 |3432       |NULL      |
|987654 |1122       |2021-02-21|
|987654 |3322       |2021-01-21|

the desired result should be the below

|Sub_ID |Cust_ID    |C_Date    |
|-------+-----------+----------|
|123456 |3432       |NULL      |
|987654 |1122       |2021-02-21|

I tried the below code but it didn’t work

    Subs_ID,
    CASE
        WHEN C_Date IS NULL THEN Cust_ID
        ELSE (FIRST_VALUE(Cust_ID) OVER (PARTITION BY Subs_ID ORDER BY C_Date DESC )) END AS Cust_ID_N
    FROM
        tbl

Answer

Netezza supports the SQL Standard NULLS FIRST syntax. I would recommend that you use that:

select t.*
from (select t.*,
             row_number() over (partition by sub_id order by c_date desc nulls first) as seqnum
      from t
     ) t
where seqnum = 1;

Advertisement

Answer