Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to databases, but I hope you can help me to understand something.

Take part of this query:

select min(date) as date...

Assume that some of the dates are null. What effect would it have? Would the query break?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

MIN will return the smallest non-NULL value. The query won't throw an error, it will simply ignore the NULL values

SQL> with x as (
  2    select null dt from dual union all
  3    select sysdate from dual
  4  )
  5  select min(dt)
  6    from x;

MIN(DT)
---------
21-OCT-11
share|improve this answer
    
Thanks so much. –  Jeremy Oct 22 '11 at 0:56

Assume that some of the dates are null

For completeness, I'm also assuming there may be zero rows

I'm using this test data:

create table t as
select to_date('20110101', 'YYYYMMDD')+level-1 as d from dual connect by level<=5
union all select null from dual;

select * from t;
D                         
------------------------- 
01-JAN-11 00.00.00        
02-JAN-11 00.00.00        
03-JAN-11 00.00.00        
04-JAN-11 00.00.00        
05-JAN-11 00.00.00        
(null)

and considering the following cases:

  1. some rows but no null values:

    select min(d) from t where d is not null;
    MIN(D)                    
    ------------------------- 
    01-JAN-11 00.00.00      
    
  2. some rows and both null and non-null values:

    select min(d) from t;
    MIN(D)                    
    ------------------------- 
    01-JAN-11 00.00.00      
    
  3. some rows and all values are null:

    select min(d) from t where d is null;
    MIN(D)                    
    ------------------------- 
    (null)
    
  4. no rows:

    select min(d) from t where 1=2;
    MIN(D)                    
    ------------------------- 
    (null)
    
  5. an interesting edge case with group by that returns no rows:

    select min(d) from t where 1=2 group by 1;
    MIN(D)                    
    ------------------------- 
    

aside: I've haven't used date as an alias because Oracle complains (not unreasonably IMO):

select to_date('20110101', 'YYYYMMDD') as date from dual;
ORA-00923: FROM keyword not found where expected

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.