Get min date across two rows in SQL table

Question

I have a table that looks like the below in SQL

I would like to select the row with the earliest date in either the PR create date or AC create date field. So in this case, I would want these two records.

I know that I can write

Select email, min(PR_create_date) from table 
group by email

However, this will only find the minimum date in the PR_create_date column. How can I find the minimum date in either the PR_create_date or AC_create_date columns?

Answer 1

You can use

SELECT email,
       MIN(least(pr_create_date, ac_create_date))
  FROM my_table
 GROUP BY email

Function least returns the minimal value of its arguments.

But if one of arguments is NULL then the result is NULL too. So you are to write some logic to handle nulls, depending on how nulls must be treated in your business logic: for example you can use "special date" in the far past or future to replace nulls as

SELECT email,
       MIN( least( ifnull(pr_create_date, DATE '2999-12-31'),
                   ifnull(ac_create_date, DATE '2999-12-31')) )
  FROM my_table
 GROUP BY email

But in the simplest case when dates are excluding (i.e. in every row exactly one of the dates is null) its enough to just write

SELECT email,
       MIN( ifnull(pr_create_date, ac_create_date) )
  FROM my_table
 GROUP BY email

Answer 2

One more solution :

 select temp.EMAIL, min(temp.PR_AC_CREATE_DATE)
 from
 (
  select EMAIL , min(PR_CREATE_DATE) as PR_AC_CREATE_DATE
  from table
  group by email

  union

  select EMAIL, min(AC_CREATE_DATE)
  from table
  group by email
  ) temp

 group by temp.EMAIL ;