Oracle SQL - Max() with Null Values

Oracle SQL - max() with NULL values

max(end_dt) keep (dense_rank first order by end_dt desc nulls first)

upd:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t
    (val int, s date, e date)
;

INSERT ALL 
    INTO t (val, s, e)
         VALUES (1, sysdate-3, sysdate-2)
    INTO t (val, s, e)
         VALUES (1, sysdate-2, sysdate-1)
    INTO t (val, s, e)
         VALUES (1, sysdate-1, null)
    INTO t (val, s, e)
         VALUES (2, sysdate-1, sysdate-.5)
    INTO t (val, s, e)
         VALUES (2, sysdate-.5, sysdate-.25)
SELECT * FROM dual
;

Query 1:

select val, min(s), max(e) keep (dense_rank first order by e desc nulls first)
from t group by val

Results:

| VAL |                          MIN(S) | MAX(E)KEEP(DENSE_RANKFIRSTORDERBYEDESCNULLSFIRST) |
---------------------------------------------------------------------------------------------
|   1 | November, 13 2012 14:15:46+0000 |                                            (null) |
|   2 | November, 15 2012 14:15:46+0000 |                   November, 16 2012 08:15:46+0000 |

Oracle SQL - get the row with either NULL or the max value

You want the ID for the highest date, where null is considered higher than any real date. In case of a tie you want the minimum ID. You can use Oracle's KEEP LAST for this:

select min(id) keep (dense_rank last order by date nulls last) from mytable;

Oracle - Finding Max (date) when column could have null values and NULL is the max date

One way is to use an analytic function like row_number in a subquery.

Something like this:

Sample data

create table t (id number, expiry date);

insert into t (id, expiry) values (3, sysdate);
insert into t (id, expiry) values (8, null);

Relevant subquery

select id, expiry, row_number() over (order by expiry desc) as rn
from   t;

        ID EXPIRY                          RN
---------- ----------------------- ----------
         8                                  1
         3 01.03.2022 20:27:45              2

Note that order by ... desc by default includes the option nulls first - so this does exactly what you need.

In the outer query, you just need to select where rn = 1:

select id, expiry
from   (
         select id, expiry, row_number() over (order by expiry desc) as rn
         from   t
       )
where  rn = 1;

        ID EXPIRY                 
---------- -----------------------
         8                        

I asked for clarification regarding "ties". If they are possible in your data, you must clarify the desired handling. For example, if you must return all rows with the "latest date", then you should use rank() instead of row_number(). For example:

Add one more row to the table

insert into t (id, expiry) values (9, null);

Modified query:

select id, expiry
from   (
         select id, expiry, rank() over (order by expiry desc) as rn
         from   t
       )
where  rn = 1;

        ID EXPIRY                 
---------- -----------------------
         8                        
         9   

Note that the Oracle optimizer is smart in such queries - it will not, in fact, perform a full ordering of all rows by date. The plan will include a window sort pushed rank operation, which does just the minimum work needed to find the rows where the rank (or row number) is 1; it doesn't fully order all rows by the ordering column (expiry in my examples).

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 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]

SQL select MAX value or NULL

You can try selecting what you want, excluding duplicates, and then doing a union, similar to this;

SELECT VAL, MAX(DAT) FROM T1 
WHERE VAL NOT IN (select VAL from T1 where DAT is NULL GROUP BY VAL, DAT)
GROUP BY VAL, DAT
UNION
select VAL, DAT from T1 where DAT is NULL GROUP BY VAL, DAT

---

Related Topics