GROUP BY last record by month and year how to do?

2

I'm doing a query in the database, 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     

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

that is, 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         
    
asked by anonymous 18.12.2018 / 19:07

3 answers

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's the example in DbFiddle: link

    
18.12.2018 / 19:37
0

You can do this by using an auxiliary table temp:

--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
    
18.12.2018 / 19:35
0

According to this SOEN question, it is possible 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've created this sample fiddle for validation.

    
18.12.2018 / 19:35