Search in the most recent year

[joon1]

id year country 1 (918) Goryeo Dynasty 2 (1368) Ming Dynasty 1 (1392) Joseon Dynasty 2 (1616) Qing Dynasty 3 (1721) Imperial Russa 4 (1815) German Confederation 4 (1871) German Empire 1 (1897) Korean Empire 2 (1912) Republic of China 4 (1919) Weimar Republic 4 (1933) Nazi Germany 4 (1945) West/East Germany 1 (1945) South/North Korea 2 (1949) People's Republic of China 3 (1922) Soviet Union 4 (1990) Federal Republic of Germany 3 (1991) Russian Federation

I have myTable like the above.

SELECT id, year, country FROM myTable WHERE country like '%$keyword%'

When the keyword is “o”, The code above produces the result follow.

1 (918) Goryeo Dynasty 1 (1392) Joseon Dynasty 4 (1815) German Confederation 1 (1897) Korean Empire 2 (1912) Republic of China 1 (1945) South/North Korea 2 (1949) People's Republic of China 3 (1922) Soviet Union 4 (1990) Federal Republic of Germany 3 (1991) Russian Federation

I like to retrieve records which have the keyword “o” in “country” field only in the most recent year among each ID.

my target result is the follow.

1 (1945) South/North Korea 4 (1990) Federal Republic of Germany 2 (1949) People's Republic of China

in case of id=3, It has the keyword “o” in Soviet Union(1922), but not to be retrived because the most recent year
Russian Federation(1991) has not the keyword “o”,

The code below doesn’t work correctly, but I hope it shows what I want.

SELECT id, year, country FROM myTable WHERE country(in limit 1 DESC) like '%$keyword%' GROUP BY id

[DaveMaxwell]

You need to filter out the latest iteration of each id before looking at the country. Untested, but this is the general direction I think you’ll need to go…

SELECT c.id
     , c.year
     , c.country
  FROM country c
  JOIN (SELECT id
             , MAX(year) as maxYear
          FROM country
         GROUP BY id) sq ON c.id = sq.id AND c.year = sq.maxYear
 WHERE country LIKE '%$keyword%'