Query for extracting latest year data for each country

[msadiqrajani]

Hi,


I have big dataset in following manner.

Country	Year	Value
Pakistan	2000	4
Srilanka	2001	2
Australia	2001	3
Srilanka	2002	2
Pakistan	2002	5
Australia	2000	7
China	1999	4
Pakistan	2001	5
China	2000	7
Srilanka	2000	8

I need to extract data for each country for latest year...
So final dataset I will get will be

Country	Latest Year	Value
Pakistan	2002	5
Srilanka	2002	2
China	2000	7
Australia	2001	3

What will be the query to extract latest year for each country...

[recyan]

Just check out if below gives some guidelines :

SELECT 
    qryCountryMaxYearA.Country, 
    qryCountryMaxYearA.MaxOfTheYear, 
    myTable.Country, 
    myTable.TheYear, 
    myTable.TheValue
FROM 
    ( 
        SELECT 
            myTable.Country, 
            Max(myTable.TheYear) AS MaxOfTheYear
        FROM 
            myTable
        GROUP BY 
            myTable.Country
    ) 
    AS qryCountryMaxYearA 
    INNER JOIN 
    myTable 
    ON 
    (qryCountryMaxYearA.MaxOfTheYear = myTable.TheYear) 
    AND 
    (qryCountryMaxYearA.Country = myTable.Country);

Thanks