Display the MAX date for each record in a WHERE IN clause SQL

Question

Using MySQL, I have the following table called subscriptions shown below

name,   renewal_date,    national_id
---------------------------------------
ted,     1/2/2010,        A1111R
ben,     1/3/2010,        A145E1
bob,      8/4/2009,       A11C11
kate,     2/2/2010,       A111E1
ted,     12/2/2011,       A1111R
bob,     12/2/2014,       A11C11
ben,     12/2/2016,       A145E1

etc..

The table has about 150000 records. Each person can have multiple subscriptions. I want to display the maximum/latest renewal_date for each record in a list given to me for querying. Here is my sql statement:

select d.name, d.renewal_date, d.national_id
from subscriptions d
where renewal_date= (select max(d1.renewal_date) 
                     from subscriptions d1 
                     where d1.national_id = d.national_id IN ('A1111R','A11C11', 'A145E1' ....));

When I run the query in phpmyadmin, it does not seem to end executing, even when the records in the IN clause are few.

Which is the best way to do it? I should also say am not an SQL expert yet :-) Thanks in advance

For MySQL it would be good to know the version you are using. There have been important changes made lately. Some things are much easier to achieve in MySQL 8 than in previous versions. — Thorsten Kettner, Nov 09 '19 at 13:20
Can there be two different national_id for the same name in the table? If so, do you want one result row for the name or one result row per name and national_id? — Thorsten Kettner, Nov 09 '19 at 13:27
Thanks @ThorstenKettner. The MySQL version is 5.7.25. And no, there can't be two different national_ids for the same name in the table. — adamvanbart, Nov 09 '19 at 13:36
Then it's time you fix your database design. One table for the persons with their national ID, one for the subscriptions. — Thorsten Kettner, Nov 09 '19 at 15:24

score 1 · Accepted Answer · answered Nov 09 '19 at 12:34

1

Assuming the names and national ids don't change for a given person, you can just use aggregation:

select d.name, max(d.renewal_date), d.national_id
from subscriptions d
where d.national_id in ( . . . )
group by d.name, d.national_id;

If you want all columns on the row, the most efficient method is typically a correlated subquery with the right index:

select s.*
from subscriptions s
where s.national_id in ( . . . ) and
      s.renewal_date = (select max(s2.renewal_date)
                        from subscriptions s2
                        where s2.national_id = s.nation_id
                       );

The right index is (national_id, renewal_date). Actually, this is probably faster than the group by query as well under many circumstances.

answered Nov 09 '19 at 12:34

Gordon Linoff

1,242,037
58
646
786

Thank you so much @gordon-linoff for the great answer and the quick response. The second example worked for me. Finally, If I wanted to delete those renewal_dates I have displayed, how would I convert from `select` to `delete`? Something like `delete s.renewal_date from...`? – adamvanbart Nov 09 '19 at 13:16
@adamvanbart: In order to delete those rows, simply change `select s.*`to `delete`. Or do you want to update the rows and only delete (i.e. make null) their date? – Thorsten Kettner Nov 09 '19 at 13:23
Thanks @gordonlinoff. I don't want to delete the entire row, but only the `renewal_date` – adamvanbart Nov 09 '19 at 13:33
1

Then change `select s.* from subscriptions s` to `update subscriptions s set renewal_date = null`. – Thorsten Kettner Nov 09 '19 at 15:26
Thanks @ThorstenKettner. However MySQL throws an error: `#1093 - You can't specify target table 's' for update in FROM clause` – adamvanbart Nov 09 '19 at 21:57
Ah, that's an odd MySQL flaw with a table being specified twice in an update statement. You can circumvent this by replacing `from subscriptions s2` by `from (select * from subscriptions) s2`. See here: https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause – Thorsten Kettner Nov 10 '19 at 08:14

score 0 · Answer 2 · answered Nov 09 '19 at 13:14

You want the maximum renewal_date per name. If that's it, then all you need to do is aggregate:

select name, max(renewal_date)
from subscriptions
group by name
order by name;

I don't know, how the national_id comes into Play. Can a person's national_id really change from one subsription to another? Or is this a defect in your database design?

You may want this:

select name, national_id, max(renewal_date)
from subscriptions
group by name, national_id
order by name, national_id;

or this:

select name, max(renewal_date), any_value(national_id)
from subscriptions
group by name
order by name;

or this:

select *
from subscriptions
where (name, renewal_date) in
(
  select name, max(renewal_date)
  from subscriptions
  group by name
)
order by name;

Display the MAX date for each record in a WHERE IN clause SQL

2 Answers2