phpmyadmin Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search