Fetch the row which has null or else the Max value for a column

Question

I need to get all users from the database. When a user gets deleted, their column deletionDate is populated with a date. When a deleted user is recreated, there is a new row created.

I need to retrieve all latest "versions" of each user, which means a row for every email. I wanted to get the one with deletionDate at null if it exists else the one with the latest deletionDate if no null row exists.

It's a mix between this Fetch the row which has the Max value for a column

And this : MySQL return max value or null if one column has no value

The pseudo table:

   ID    EMAIL   DELETE_DATE     

    1    mail1   2016-09-08 09:56:21
    2    mail1   2016-19-08 09:56:22
    3    mail1                         < SELECTED
    4    mail2   2017-19-08 09:56:22
    5    mail2   2018-19-08 09:56:22   < SELECTED
    6    mail3   2018-15-08 09:56:22   < SELECTED
    7    mail4                         < SELECTED

SELECTED denotes the list of rows I would like to get in my query.

My DBMS is MySQL but if there is a way to do with JPA, it would be better and fit the criteria even better. Also I don't want to cherry-pick fields; I want the real rows.

please show some sample data and the expected output. – Vamsi Prabhala Apr 12 '17 at 14:05 — Vamsi Prabhala, Apr 12 '17 at 14:05
done as you posted your comment ;) – TheBakker Apr 12 '17 at 14:11 — TheBakker, Apr 12 '17 at 14:11

score 1 · Answer 1 · answered Apr 12 '17 at 14:06

1

If you have a single user, you can do:

select t.*
from t
where t.userid = $userid  -- if this is called from app code, use a parameter
order by (deletionDate is null) desc,
         deletionDate desc;

answered Apr 12 '17 at 14:06

Gordon Linoff

1,242,037
58
646
786

Hey, I need to get all the users in one query sadly. – TheBakker Apr 12 '17 at 14:10

score 1 · Accepted Answer · answered Apr 12 '17 at 14:39

1

Assuming there can only be one row per email where delete_date is null,you can use union all.

select email,delete_date
from t
where delete_date is null
union all
select email,max(delete_date)
from t t1
where not exists (select 1 from t t2 where t1.email=t2.email and t2.delete_date is null)
group by email

answered Apr 12 '17 at 14:39

Vamsi Prabhala

48,685
4
36
58

Aye that's close to what I ended up doing. I wanted to avoid UNION to be able to do the query with JPA and I also wanted to get the whole row and not specific fields. – TheBakker Apr 12 '17 at 14:56

TheBakker · Answer 3 · 2017-04-12T15:06:59.587

Here is what I ended up doing as I wanted to avoid Union since it's not available in JPA and I wanted to get the full rows.

 select * from USER
 where (DELETE_DATE is null
 OR ID in (select ID from USER where (EMAIL, DELETE_DATE) in 
 (select EMAIL, max(DELETE_DATE) from USER where EMAIL not in 
 (select EMAIL from USER where DELETE_DATE is null) group by EMAIL )));

If there is a simplier or better way to do it, I'm interested.

Fetch the row which has null or else the Max value for a column

3 Answers3