Annie Jeba Annie Jeba - 26 days ago 5
SQL Question

Max function to return null in Oracle

For below input

Employee_ID Date_Column
100 12/12/2016
100 15/12/2016
200 19/12/2016


i used query

select employee_id,Max(Date_Column),Min(Date_Column) from Employee_ID where Date_Column is not null Group by Employee_ID


to retrieve Max and Min dates

But if my date_column has only one date for an employee both Max and Min function are returning same dates.

But my expected output is

employee_id Max(Date_Column) Min(Date_Column)
100 15/12/2016 12/12/2016
200 NULL 19/12/2016


Your help is much appreciated

Answer

With only one value, the Oracle code is correct. You can do what you want with conditional logic:

select employee_id,
       (case when max(date_column) <> min(date_column) then Max(Date_Column) end),
       Min(Date_Column)
from Employee_ID
where Date_Column is not null
Group by Employee_ID;
Comments