How to get the last amount paid grouped by city and year?

I would like to know how to get the amounts paid for the last two months paid per city ordered per year.

If I group by year, city name and two months :

select year, nameCity, twoMonths, payment
From t
group by year, nameCity, twoMonths

I would have something like this :

2018 :  city1 :  2   200
                 4   300
                 6   400

        city2 :  2   250
                 4   100


2019 :  city1 :  1   100
                 2   200
                 3   500

        city2 :  2   750
                 4   180
                 6   220    

But the output I want is something like this:

2018    city1   6   400 
        city2   4   100

2019   city1    3   500 
       city2    6   220 

Does anyone know how I can do this?

Answer

You may use a subquery to find the most values of twoMonths for each year and city, and the join to it:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT year, nameCity, MAX(twoMonths) AS maxTwoMonths
    FROM yourTable
    GROUP BY year, nameCity
) t2
    ON t1.year = t2.year AND
       t1.nameCity = t2.nameCity AND
       t1.twoMonths = t2.maxTwoMonths

If your version of SQL support ROW_NUMBER, then it can provide an alternative:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY year, nameCity
                                 ORDER BY twoMonths DESC) rn
    FROM yourTable
)

SELECT year, nameCity, twoMonths, payment
FROM cte
WHERE rn = 1;