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
– Vamsi Prabhala

2017-04-12 14:05:19 +00:00
Commented Apr 12, 2017 at 14:05 — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 12, 2017 at 14:05
done as you posted your comment ;)

TheBakker
– TheBakker

2017-04-12 14:11:30 +00:00
Commented Apr 12, 2017 at 14:11 — TheBakker
– TheBakker, Commented Apr 12, 2017 at 14:11

Vamsi Prabhala · Accepted Answer · 2017-04-12 14:39:01Z

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, 2017 at 14:39

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheBakker Over a year ago

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.

Gordon Linoff · Accepted Answer · 2017-04-12 14:06:44Z

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, 2017 at 14:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

TheBakker Over a year ago

Hey, I need to get all the users in one query sadly.

TheBakker · Accepted Answer · 2017-04-12 15:06:59Z

0

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.

edited Apr 12, 2017 at 15:06

answered Apr 12, 2017 at 14:54

TheBakker

3,1423 gold badges36 silver badges52 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related