Retrieving the last record not the first from in group-CodePudding

I have table like

Country_d  Country      Code Year Month Index 
    2       Germany     DEU  2020   9   
    2       Germany     DEU  2020   10  
    2       Germany     DEU  2020   11   58
    2       Germany     DEU  2020   12   72
    2       Germany     DEU  2021   1    61
    2       Germany     DEU  2021   2    39
    2       Germany     DEU  2021   3    38
    2       Germany     DEU  2021   4

My query is like this

SELECT * FROM ( SELECT d.year, d.month, d.country_id as value
    FROM `general` d
    INNER JOIN units c ON c.id = d.country_id
    WHERE d.country_id IN (185) 
    ORDER BY c.unit_en, d.year DESC, d.month DESC ) `data`
GROUP BY country_id

The query returns

Country   Code    Year   Month   Index
======================================
Germany   DEU     2020     11      58

What must return is the latest updated month and index. In this case must return

Country   Code    Year   Month   Index
======================================
Germany   DEU     2021     3      38

because this is the latest updated month with Index

I have tried to add MAX() for the d.year and d.month like SELECT MAX(d.year), MAX(d.month), d.country_id as value but then for the month return month 4 which doesn't have index.

When I remove GROUP BY country_id it shows all data too..

Any suggestions how can I change the query?

CodePudding user response：

Use ROW_NUMBER to calculate a sequencial number per country.
Then the 1st is what you want.

SELECT * 
FROM 
( 
    SELECT d.year, d.month, d.country_id
    , c.unit_en
    , ROW_NUMBER() OVER (PARTITION BY d.country_id ORDER BY d.year DESC, d.month DESC) AS rn
    FROM `general` d
    INNER JOIN units c ON c.id = d.country_id
    WHERE d.country_id IN (185)
    AND d.Index IS NOT NULL
) `data`
WHERE rn = 1