GROUP BY last registration per month and year how to do?

Question

I’m making an appointment at the bank, follow my table below

id | valor  |  mes |  ano  | 
1    39.69      3    2017     
1    7.69       3    2018     
3    9.69       4    2015     
3    3.69       2    2016     
2    5.89       3    2017    
2    39.69      8    2018     

need to bring the result by grouping the values by the last month and year,

I mean, I need the result like this:

id | valor  |  mes |  ano  |    
1    7.69       3    2018         
3    3.69       2    2016   
2    39.69      8    2018         

Answer 1

I think this way you get what you want:

SELECT      T.*
FROM        Teste T
INNER JOIN  (
                SELECT      Id
                        ,   MAX(Ano) AS Ano
                FROM        Teste
                GROUP BY    Id) X   ON  X.Id    = T.Id 
                                    AND X.Ano   = T.Ano
INNER JOIN  (
                SELECT      Id
                        ,   Ano
                        ,   MAX(Mes) AS Mes
                FROM        Teste
                GROUP BY    Id
                        ,   Ano) X2 ON  X2.Id   = T.Id 
                                    AND X2.Ano  = T.Ano 
                                    AND X2.Mes  = T.Mes
ORDER BY    T.Id

Here is the example in Dbfiddle: https://www.db-fiddle.com/f/sAVS8KHcs7HS39fWnRGCT8/0

Answer 2

You can do this using an auxiliary temp table:

--tabela fake
/*
DECLARE @TABLE TABLE (ID INT, Valor FLOAT, Mes INT, Ano INT)

--valores fake 
INSERT INTO @TABLE VALUES(1, 39.69,3, 2017)    
INSERT INTO @TABLE VALUES(1, 7.69 ,3, 2018)    
INSERT INTO @TABLE VALUES(3, 9.69 ,4, 2015)    
INSERT INTO @TABLE VALUES(3, 3.69 ,2, 2016)    
INSERT INTO @TABLE VALUES(2, 5.89 ,3, 2017)   
INSERT INTO @TABLE VALUES(2, 39.69,8, 2018)
*/

SELECT ID, MAX(Ano) AS Ano
INTO #Temp
  FROM @TABLE
 GROUP BY ID

SELECT T2.*
  FROM #Temp T
  JOIN @TABLE T2 ON T2.ID = T.ID AND T.Ano = T2.Ano


 --DROP TABLE #Temp

Answer 3

According to this question of SOEN, it is possible to use MAX along with GROUP BY; then you can do the following:

SELECT id, valor, mes, ano, CONCAT(ano, mes) as anoMes
FROM tabela
GROUP BY id

I created this fiddle of example for validation.