Filter data based on group SQL Netezza

Question

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

forpas · Accepted Answer · 2021-02-21 10:27:23Z

2

You can use ROW_NUMBER() window function with a CASE expression in the ORDER BY clause:

SELECT Subs_ID, Cust_ID, C_Date
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
                        PARTITION BY Subs_ID 
                        ORDER BY CASE WHEN C_Date IS NULL THEN 1 ELSE 2 END, C_Date DESC 
                      ) rn
  FROM tablename
) t
WHERE rn = 1

The ORDER BY clause can be also simplified to:

ORDER BY C_Date IS NULL DESC, C_Date DESC

See the demo (for MySql but it is standard SQL).
Results:

Subs_ID	Cust_ID	C_Date
123456	3432	null
987654	1122	2021-02-21

edited Feb 21, 2021 at 10:27

answered Feb 21, 2021 at 10:20

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmed Mohammed Abdel Kader Over a year ago

thanks a lot, @forpas for your great support

Gordon Linoff · Accepted Answer · 2021-02-21 12:53:03Z

1

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;

answered Feb 21, 2021 at 12:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Filter data based on group SQL Netezza

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related