 Ignore nulls in SQL
By : Alireza_Armn
Date : March 29 2020, 07:55 AM
I wish this helpful for you Your test data suggests that the TIME and TYPE are linked, and that the values always rise with time. In which case this solution will work: code :
select Time
, Type
, max(Value_A) as Value_A
, max(Value_B) as Value_B
from your_table
group by Time
, Type
select distinct Time
, Type
, last_value(Value_A ignore nulls)
over (partition by time, type order by Loaded_time_sequence
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Value_A
, last_value(Value_B ignore nulls)
over (partition by time, type order by Loaded_time_sequence
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Value_B
from t23
;
|
 Format datetime field as MON-YYYY in python pandas & ignore nulls
By : user3377413
Date : March 29 2020, 07:55 AM
|
Order by min of two dates with nulls first for one column, ignore nulls for another
By : Puspahas Das
Date : March 29 2020, 07:55 AM
hop of those help? I want to order my data by the minimum date of two columns, with equal precedence. If Date1 has nulls, I want them to appear first. If Date2 has nulls, I just want to go by Date1. If both dates are null, it should appear at the top. , LEAST and COALESCE should give you what you're after: code :
with sample_data as (select to_date('01/01/2012', 'dd/mm/yyyy') date1, to_date('01/01/2000', 'dd/mm/yyyy') date2 from dual union all
select null date1, null date2 from dual union all
select to_date('01/01/2009', 'dd/mm/yyyy') date1, to_date('01/01/2015', 'dd/mm/yyyy') date2 from dual union all
select to_date('01/01/2013', 'dd/mm/yyyy') date1, null date2 from dual union all
select to_date('01/01/2003', 'dd/mm/yyyy') date1, to_date('01/01/2003', 'dd/mm/yyyy') date2 from dual)
select date1,
date2
from sample_data
order by least(date1, coalesce(date2, date1)) nulls first;
DATE1 DATE2
---------- ----------
01/01/2012 01/01/2000
01/01/2003 01/01/2003
01/01/2009 01/01/2015
01/01/2013
|
 GROUP BY but ignore nulls unless no not-nulls
By : Wiinterfell
Date : March 29 2020, 07:55 AM
it helps some times Imagine you have two lists; a list of drivers and a list of vehicles. You want the complete list of drivers, plus any vehicle records if they exist. code :
SELECT drivers.driver_uuid,
vehicles.vehicle_uuid,
vehicles.document_uuid
FROM driver_vehicle_documents drivers
LEFT JOIN driver_vehicle_documents vehicles ON vehicles.driver_uuid = drivers.driver_uuid
AND vehicles.vehicle_uuid IS NOT NULL
|
Replacing nulls by fillna still returns nulls in pandas
By : Dave Magno
Date : March 29 2020, 07:55 AM
it fixes the issue Inplace fillna does not work on pd.Series columns because they return a copy, and the copy is modified, leaving the original untouched. Why not just do - code :
df.loc[:, 'A':'D'] = df.loc[:, 'A':'D'].fillna('Global')
df.loc[:, ['A', 'B', 'C', 'D']] = df.loc[:, ['A', 'B', 'C', 'D']].fillna('Global')
|
code :
Related Posts :