How to select multiple rows by multi-column primary key in MySQL? -

i have table multi-column primary key (city/state/date) , many more columns of data. i'm looking latest data each city/state. how do cleanly/efficiently? right can doing first query list of rows i'm trying fetch, followed second query massive clause:

 select state, city, max(date) data group city, state;  +-------+---------------------+------------+ | state | city                | max(date)  | +-------+---------------------+------------+ | ca    | san francisco       | 2013-09-01 | | ca    | los angeles         | 2013-08-01 | | ny    | new york            | 2013-10-01 | | ...   | ... (many rows) ... | ...        | +-------+---------------------+------------+   select * data      (state = "ca" , city = "san francisco" , date='2013-09-01') or      (state = "ca" , city = "los angeles" , date='2013-08-01') or      (state = "ny" , city = "new york" , date='2013-10-01') or      ... 

this ugly , inefficient, , if first query returns lot of rows second query might long. if have single-column primary key use subselect in(), that's not possible here. suggestions?

update: tried bill's suggestion subselect, it's not using keys , taking forever. if restrict subselect return 5 rows returns in 0.64s. if let return 73 city/state combinations, takes long time (query still running).

explain select * data (city, state, date) in (select state, city, max(date) data group city, state) +----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+ | id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows  |       | +----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+ |  1 | primary            | data  |   | null          | null    | null    | null | 13342 | using | |  2 | dependent subquery | data  | index | null          | primary | 57      | null |  8058 | using index | +----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+ 

i think should trick you:

select      *       data t1 natural join      (          select              city,              state,              max(date) date                      data         group              city,              state     ) t2;