[2008R2] Recuperer la valeur avec la plus grande date

SELECT "Societe" ,"Atelier", "tarif",MAX("date_debut") FROM (SELECT tgempresa.razon as "Societe", tgtaller.descrip as "Atelier", ttTarifa.Descrip as "tarif", ttTarifaPrecioFecha.Tecnicidad as "M" , ttTarifaPrecioFecha.FechaDesde as "date_debut", ttTarifaPrecioFecha.PrecioHora as "prix" FROM ttTarifaPrecioFecha inner join tgempresa on ttTarifaPrecioFecha.emp = tgempresa.emp inner join tgtaller on ttTarifaPrecioFecha.emp = tgtaller.emp and ttTarifaPrecioFecha.taller = tgtaller.taller INNER JOIN ttTarifa on ttTarifa.Codigo=ttTarifaPrecioFecha.Codigo WHERE ( ttTarifaPrecioFecha.Tecnicidad = 'M2' ) AND ttTarifaPrecioFecha.Codigo IN ('TCE','TCL','TGA') GROUP BY tgempresa.razon, tgtaller.descrip, ttTarifa.Descrip, ttTarifaPrecioFecha.Tecnicidad, ttTarifaPrecioFecha.FechaDesde,ttTarifaPrecioFecha.PrecioHora) requete1 GROUP BY "Societe" ,"Atelier","tarif","date_debut"

Déjà votre requête est particulièrement mal écrite :

1) supprimez les " parasites
2) supprimez les ( ou ) inutiles
3) utilisez des alias de table
4) identez correctement votre requête
5) supprimez les GROUP BY inutiles

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT Societe ,Atelier, tarif, MAX(date_debut)
FROM   (SELECT G.razon AS Societe, 
               T.descrip AS Atelier,
               R.Descrip AS tarif,     
               TPF.Tecnicidad AS M ,          
               TPF.FechaDesde AS date_debut,
               TPF.PrecioHora AS prix
        FROM   ttTarifaPrecioFecha AS TPF
               INNER JOIN tgempresa AS G
                     ON  TPF.emp = G.emp 
               INNER JOIN tgtaller AS T
                     ON  TPF.emp = T.emp 
                         AND TPF.taller = G.taller  
               INNER JOIN ttTarifa AS R
                     ON R.Codigo=TPF.Codigo  
        WHERE  TPF.Tecnicidad = 'M2' 
          AND  TPF.Codigo IN ('TCE','TCL','TGA')) AS requete1
GROUP BY Societe, Atelier, tarif, date_debut;

Ensuite le résultat est normal puisqu'il y a des tarifs différents pour des dates différentes !

Si vous voulez le tarif à la date la plus récente il vous faut faire une sous requête et éventuellement utiliser les fonctions de fenêtrage.

Au passage utiliser la CTE serait plus lisible !

Exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH 
requete1 AS 
(SELECT G.razon AS Societe, 
        T.descrip AS Atelier,
        R.Descrip AS tarif,     
        TPF.Tecnicidad AS M ,          
        TPF.FechaDesde AS date_debut,
        TPF.PrecioHora AS prix
 FROM   ttTarifaPrecioFecha AS TPF
        INNER JOIN tgempresa AS G
              ON TPF.emp = G.emp 
        INNER JOIN tgtaller AS T
              ON TPF.emp = T.emp 
                 AND TPF.taller = G.taller  
        INNER JOIN ttTarifa AS R
              ON R.Codigo=TPF.Codigo  
 WHERE  TPF.Tecnicidad = 'M2' 
   AND  TPF.Codigo IN ('TCE','TCL','TGA')),
requete2 AS 
(SELECT Societe ,Atelier, tarif, date_debut,
        RANK() OVER(PARTITION BY Societe ,Atelier ORDER BY date_debut) AS RANG 
 FROM   requete1)
SELECT Societe ,Atelier, tarif, date_debut
FROM   requete2
WHERE  RANG = 1;

A +