CODEWITHSUNDEEP
mysqlsql
TheBakker
TheBakker

Reputation: 3052

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

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.

Upvotes: 0

Views: 224

Answers (3)

TheBakker
TheBakker

Reputation: 3052

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.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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;

Upvotes: 1

Related Questions