Need help to get the sql query right. I have data as below -
|Order Number||Order Line||Despatch date|
Expected output - If the despatch date has 'Null' even for one line(Orderline) within an order then 'NULL' else give me the maximum of despatch date.
So in this case expected output is
|Order Number||Despatch date|
Order line not required in Output , shown only for the sake of granularity.
I tried - (rough sql - excuse the syntax /name mismatch)
case when Despatch_Date is NULL then NULL else max("Despatch_Date") Over(Partition by "Order Number") end as "daternk"
from Table A
group by Order Number , Despatch_date
This sql returns me two records one for NULL and other max despatch date as I have to include despatch date in group by .But I need only one despatch date of NULL(in this scrnario) for this order. It has to be a generic sql as there are other order Numbers which dont have NULLS and hence picks only the max(depsatch date).