How to Include Null Values in a Min or Max

How can I include null values in a MIN or MAX?

It's a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),
   CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
        THEN MAX(enddate)
   END
FROM tmp GROUP BY recordid

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).

How to fill NULL value using min-max in SQL Server?

find the min() and max() for the price GROUP BY cat01, cat02.
Also find the min and max seq for the row where price is not null

after that it is just simply inner join to your table and update where price is null

with val as
(
    select cat01, cat02, 
           min_price = min(price), 
           max_price = max(price),
           min_seq   = min(case when price is not null then seq end),
           max_seq   = max(case when price is not null then seq end)
    from   temp01
    group by cat01, cat02
)
update t
set    price = case when t.seq < v.min_seq then min_price
                    when t.seq > v.max_seq then max_price
                    end
FROM   temp01 t
       inner join val v on   t.cat01 = v.cat01
                        and  t.cat02 = v.cat02
where  t.price is null  

dbfiddle

EDIT : returning the price as a new column in SELECT query

with val as
(
    select cat01, cat02, min_price = min(price), max_price = max(price),
           min_seq = min(case when price is not null then seq end),
           max_seq = max(case when price is not null then seq end)
    from   temp01
    group by cat01, cat02
)
select t.*,
       new_price = coalesce(t.price,
                            case when t.seq < v.min_seq then min_price
                                 when t.seq > v.max_seq then max_price
                                 end)
FROM   temp01 t
       left join val v on t.cat01 = v.cat01
                        and  t.cat02 = v.cat02

Updated dbfiddle

Take min and max with null values - pandas groupby

IIUC,DataFrame.mask to set NaN where there are any nan for each group and col

new_df = \
df.groupby('id')\
  .agg({'start':'min', 'end':'max'})\
  .mask(df[['start', 'end']].isna()
                            .groupby(df['id'])
                            .max())\
  .reset_index()

print(new_df)
  id               start        end
0  a 2020-01-01 00:00:00 2020-01-02
1  b 2020-01-01 18:37:00        NaT
2  c 2020-02-04 00:00:00 2020-07-13
3  d 2020-04-19 20:45:00 2021-03-02

Detail:

print(df[['start', 'end']].isna()
                            .groupby(df['id'])
                            .max())

    start    end
id              
a   False  False
b   False   True
c   False  False
d   False  False

---

In the case of multiple columns to group by:

new_df = \
df.groupby(['id', 'status'])\
  .agg({'start':'min', 'end':'max'})\
  .mask(df[['start', 'end']].isna()
                            .groupby([df['id'], df['status']])
                            .max())\
  .reset_index()

How to find max value of column with NULL being included and considered the max

Try this :

SELECT [ID]
     , CASE WHEN MAX(CASE WHEN [Date] IS NULL THEN 1 ELSE 0 END) = 0 THEN MAX([Date]) END
FROM YourTable
GROUP BY [ID]

NULL in MAX,MIN function in HIVE

You can create a flag that says there are NULLs in your key-group. Then you can aggregate that newly created column and look for the presence of your flag, indicating there is at least one NULL in the group.

Data:

key     val
-----------
A       1
A       NULL
B       3  
B       2
C       NULL
C       10
C       4

Query0:

SELECT key
  , CASE WHEN ARRAY_CONTAINS(cs, 1) THEN NULL ELSE m END AS col_max
FROM (
  SELECT key
    , MAX(val) AS m
    , COLLECT_SET(CASE WHEN val IS NULL THEN 1 ELSE 0 END) AS cs
  FROM database.table
  GROUP BY key ) x;

You can also use SUM() (or MAX()) instead of using COLLECT_SET().

Query1:

SELECT key
  , CASE WHEN cs > 0 THEN NULL ELSE m END AS col_max
FROM (
  SELECT key
    , MAX(val) AS m
    , SUM(CASE WHEN val IS NULL THEN 1 ELSE 0 END) AS cs
  FROM database.table
  GROUP BY key ) x;

Output:

key    col_max
--------------
A      NULL 
B      3 
C      NULL

How not to include null values in min

list.Where(item => item != null).Min()

But why are you representing your numbers as string? I assume you are looking for Min of numbers, not of a string - consider changing the type

Also the following line mfn_new[i] = row.Cells[21].Value.ToString(); might throw a NullReferenceException in the case that the .Value is null so checking later on if it is null isn't relevant.

---

After you updated the question id suggest:

mfn_new[i] = row.Cells[21].Value?.ToString();
....

list.Where(item => item != string.Empty).Min()

Java validation @min @max annotion for null values

For optional integer values, you may use Integer instead of int, since an int variable cannot be null and will have 0 as a default value.
With an Integer, length will be null by default and you should be able to pass the validation.

How to handle nulls in LINQ when using Min or Max?

A short summary of the calculation of a Min

- No mediation (Exception!)

   var min = result.Partials.Where(o => o.IsPositive).Min(o => o.Result);

This is your case: if there are no matching elements, then the Min call will raise an exception (InvalidOperationException).

- With DefaultIfEmpty() -- still troublesome

 var min = result.Partials.Where(o => o.IsPositive)
                          .Select(o => o.Result)
                          .DefaultIfEmpty()
                          .Min();

DefaultIfEmpty will create an enumeration over the 0 element, when there are no elements in the list. How do you know that 0 is the Min or if 0 stands for a list with no elements?

- Nullable values; A better solution

   var min = result.Partials.Where(o => o.IsPositive)
                            .Min(o => (decimal?)o.Result);

Here Min is either null (because that's equal to default(decimal?)) or the actual Min found.

So a consumer of this result will know that:

1. When result is null then the list had no elements
2. When the result is a decimal value then the list had some elements and the Min of those elements is that returned value.

However, when this doesn't matter, then min.GetValueOrDefault(0) can be called.

---

Related Topics