I am a beginner in sql and I wanted to know how to get for each id of two joined tables the max of a couple of integer?

what I have done so far:

SELECT 
  Reduced_T_Rappro_N.[Rente RES] AS Rente_RES, 
  Reduced_T_Rappro_N.[Montant capital constitutif] AS Montant_Capital_Consitutif_N, 
  [Reduced_T_Rappro_N - 1].[Montant capital constitutif] AS [ "Montant_Capital_Constitutif_N-1" ], 
  IIf(
    [Reduced_T_Rappro_N - 1].[Montant capital constitutif] = 0, 
    0, 
    (
      [Reduced_T_Rappro_N].[Montant capital constitutif] - [Reduced_T_Rappro_N - 1].[Montant capital constitutif]
    )/ [Reduced_T_Rappro_N - 1].[Montant capital constitutif]
  )* 100 AS [ "Evolution capital constitutif" ], 
  [Reduced_T_Rappro_N].[Montant Sous Rente], 
  [Reduced_T_Rappro_N - 1].[Montant Sous Rente], 
  (
    IIf(
      [Reduced_T_Rappro_N - 1].[Montant Sous Rente] = 0, 
      0, 
      (
        [Reduced_T_Rappro_N].[Montant Sous Rente] - [Reduced_T_Rappro_N - 1].[Montant Sous Rente]
      )/ [Reduced_T_Rappro_N - 1].[Montant Sous Rente]
    )
  )* 100 AS [ "Evolution montant sous rente" ], 
  Reduced_T_Rappro_N.[Montant rente initial] AS Montant_Rente_Initial_N, 
  [Reduced_T_Rappro_N - 1].[Montant rente initial] AS [ "Montant_Rente_Initial_N-1" ], 
  (
    IIf(
      [Reduced_T_Rappro_N - 1].[Montant rente initial] = 0, 
      0, 
      (
        [Reduced_T_Rappro_N].[Montant rente initial] - [Reduced_T_Rappro_N - 1].[Montant rente initial]
      )/ [Reduced_T_Rappro_N - 1].[Montant rente initial]
    )
  )* 100 AS [ "Evolution rente initial" ], 
  MAX(
    [Reduced_T_Rappro_N - 1].[Montant Sous Rente]   [Reduced_T_Rappro_N].[Montant Sous Rente]
  ) AS [ "Addition" ] 
FROM 
  Reduced_T_Rappro_N 
  INNER JOIN [Reduced_T_Rappro_N - 1] ON (
    Reduced_T_Rappro_N.[Rente RES] = [Reduced_T_Rappro_N - 1].[Rente RES]
  ) 
  AND (
    Reduced_T_Rappro_N.[Société] = [Reduced_T_Rappro_N - 1].[Société]
  ) 
GROUP BY 
  Reduced_T_Rappro_N.[Rente RES], 
  Reduced_T_Rappro_N.[Montant capital constitutif], 
  [Reduced_T_Rappro_N - 1].[Montant capital constitutif], 
  [Reduced_T_Rappro_N].[Montant Sous Rente], 
  [Reduced_T_Rappro_N - 1].[Montant Sous Rente], 
  Reduced_T_Rappro_N.[Montant rente initial], 
  [Reduced_T_Rappro_N - 1].[Montant rente initial];

The divisions in my query correspond to calculations of evolution in percentage, moreover I use MS ACCESS from where the call of methods iif

Results:

What I want: Obtain only one result per ID (Rente RES) corresponding to the maximum of the addition between [Montant sous rente N] and [Montant sous rente N-1].

CodePudding user response：

SELECT Reduced_T_Rappro_N.Rente_RES, Max(([Montant_Capital_Consitutif_N-1] [Montant_Capital_Consitutif_N])) AS Addition FROM Reduced_T_Rappro_N GROUP BY Reduced_T_Rappro_N.Rente_RES;