Max() et inner join

Version imprimable

05/11/2012, 09h53
laloune

Max() et inner join
Bonjour à tous,

j'ai une petite question concernant la fonction max(). Voici un extrait de ma table MIETVERTRAEGE:

VERTRAGSNUMMER | MVANUMMER | KFZExchangeNo | BASENR
100493 | 10054 | 1 | 100493
200000007 | 10002 | 2 | 100493
200000008 | 10054 | 3 | 100493
200000016 | 10010 | 4 | 100493

je cherche à récupérer juste le MVANUMMER de la dernière ligne (10010), c'est à dire celle du maximum de KFZExchangeNo

lorsque je fais ca:
Code:

1 2 3 4 5 6 7 select MV.VERTRAGSNUMMER, MV.MVANUMMER, MAX(MV.KFZExchangeNo) FROM MIETVERTRAEGE MV WHERE MV.KFZChangeGroupID=8 GROUP BY MV.VERTRAGSNUMMER, MV.MVANUMMER
il me renvoie toutes les 4 lignes :-(

une idée ?

D'avance merci !
05/11/2012, 10h23
Donpi

regarder comment fonctionne les INNER APPLY et les OUTER APPLY moi j'utilise ça quand je dois le faire

sinon tu as aussi les requêtes imbriquées

Code:

1
2
3
4
5
6
7
8
 
SELECT
MV.VERTRAGSNUMMER,
MV.MVANUMMER,
MAX(MV.KFZExchangeNo) OVER(PARTITION BY 0)
FROM MIETVERTRAEGE MV
WHERE MV.KFZChangeGroupID=8
GROUP BY MV.VERTRAGSNUMMER, MV.MVANUMMER

05/11/2012, 11h02
laloune

Bonjour à vous deux,

@Donpi: merci, je vais regarder ca
@iberserk : il me dit que MIETVERTRAEGE.KFZExchangeNo doit être inclus dans la clause GROUP BY (si j'enlève le OVER(PARTITION BY 0) pas d'erreur, il me renvoie les 4 lignes)

merci !

Pardon je n'ai pas enlevé votre GROUP BY...

Code:

1
2
3
4
5
6
7
 
SELECT DISTINCT 
MV.VERTRAGSNUMMER,
MV.MVANUMMER,
MAX(MV.KFZExchangeNo) OVER(PARTITION BY 0)
FROM MIETVERTRAEGE MV
WHERE MV.KFZChangeGroupID=8

05/11/2012, 11h14
laloune

euh pardon mais je ne comprends pas, est-ce que votre requête doit renvoyer seulement la ligne :
200000016 10010

?

car il me renvoie :
VERTRAGSNUMMER MVANUMMER 3 N/A
100493 10054 4
200000007 10002 4
200000008 10054 4
200000016 10010 4
05/11/2012, 11h41
iberserk

Je ne comprends pas ce que vous voulez:
Postez le résultat que vous attendez...
05/11/2012, 12h47
laloune

Le résultat que j'essaie d'atteindre est:

VERTRAGSNUMMER | MVANUMMER | 3 N/A
200000016 | 10010 | 4

Quelque chose de ce genre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT VERTRAGSNUMMER, MVANUMMER, KFZExchangeNo
FROM (
SELECT
MV.VERTRAGSNUMMER,
MV.MVANUMMER,
MV.KFZExchangeNo,
ROW_NUMBER() OVER(ORDER BY MV.KFZExchangeNo DESC) AS rang
FROM MIETVERTRAEGE MV
WHERE MV.KFZChangeGroupID=8
) t
WHERE t.rang = 1

05/11/2012, 13h27
mikedavem
... ou éventuellement
Code:

1 2 3 4 SELECT * FROM MIETVERTRAEGE WHERE KFZExchangeNo = (SELECT MAX(KFZExchangeNo) FROM MIETVERTRAEGE WHERE KFZChangeGroupID=8) AND MV.KFZChangeGroupID=8
Mais tu ne pourrais pas passer par les agrégats comme tu essaies de faire.
Tu vas créer des groupes qui rassemblent VERTRAGSNUMMER, MVANUMMER. Hors ceci sont distincts sur les lignes concernées. C'est la raison pour laquelle tu auras tjrs 4 lignes ici.

++

Une variante :

Code:

1
2
3
4
5
6
7
8
9
10
WITH T (KFZExchangeNoMax)
AS
(
    SELECT MAX(KFZExchangeNo)
    FROM   MIETVERTRAEGE
    WHERE  KFZChangeGroupID = 8
)
SELECT M.* 
FROM   T JOIN MIETVERTRAEGE AS M ON KFZExchangeNoMax = M.KFZExchangeNo
WHERE  KFZChangeGroupID = 8 ;

05/11/2012, 19h07
laloune

Citation:

Envoyé par mikedavem

Mais tu ne pourrais pas passer par les agrégats comme tu essaies de faire.
Tu vas créer des groupes qui rassemblent VERTRAGSNUMMER, MVANUMMER. Hors ceci sont distincts sur les lignes concernées. C'est la raison pour laquelle tu auras tjrs 4 lignes ici.

++

c'est absolument corrects, c'est pourtant ce que j'essaie de faire (en fait le KFZChangeGroupID = 8 était juste un exemple pour faire mes tests...)

je vais essayer de trouver une solution.

merci à tous pour votre aide,
05/11/2012, 19h19
fsmrel

Bonsoir laloune,

En quoi la solution de mikedavem (et la mienne) ne répondent pas à votre besoin ?

Préférez-vous utiliser une vue ?

Code:

1
2
3
4
5
CREATE VIEW VUE_MAX (KFZExchangeNoMax)
AS 
    SELECT MAX(KFZExchangeNo)
    FROM   MIETVERTRAEGE
    WHERE  KFZChangeGroupID = 8 ;

Puis :

Code:

1
2
SELECT M.*
FROM   VUE_MAX AS T JOIN MIETVERTRAEGE AS M ON T.KFZExchangeNoMax = M.KFZExchangeNo ;

06/11/2012, 15h40
laloune

Bonjour,

en fait je me suis rendu compte que mon problème est un peu plus compliqué:

- d'une part j'ai filtré sur KFZChangeGroupID = 8 pour restreindre mon dataset pour pouvoir faire des tests, mais ce filtre est amené à disparaitre car je dois faire le même traitement sur toute la table

- en fait l'idée c'est que ma table MIETVERTRAEGE contient des informations relatives à des contrats de location de voiture.

Dans cette table j'ai une colonne BASENUMMER qui est le numéro de base, auxquels sont liés des modifications de contrats (une ligne par modification)

Dans notre exemple, le basenummer est 100493, et les numéros de contrats sont 100493, 200000007, 200000008, 200000016. On identifie la modification la plus récente grace à la colonne KFZExchangeNo (qui peut prendre les valeurs null, c'est à dire pas de modification, à n)

l'idée est de récupérer la ligne ayant la valeur maximale de KFZExchangeNo (afin d'avoir la modification la plus récente)

je ne sais pas trop si c'est clair, et si c'est possible de faire ca avec sql server

merci pour votre aide en tous cas !
06/11/2012, 16h22
iberserk
On reprend à zéro: commencez par respecter la charte du forum!
Ainsi:
- Postez les DLL (code de création) de votre table
- Un petit jeu de donnée,
- Le résultat escompté pour ce jeu de donnée.
Cela évitera que l'on perde inutilement notre temps!

Merci pour nous...

Si j'ai bien compris il faut sélectionner le max de KFZExchangeNo pour chaque BaseNR. Il suffit dans la requête que j'ai proposée de changer la clause partition by de la fonction de fenêtrage:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT VERTRAGSNUMMER, MVANUMMER, BASENR , KFZExchangeNo FROM ( SELECT MV.VERTRAGSNUMMER, MV.MVANUMMER, MV.KFZExchangeNo MV.BASENR, ROW_NUMBER() OVER(PARTITION BY BASENR ORDER BY MV.KFZExchangeNo DESC) AS rang FROM MIETVERTRAEGE MV WHERE MV.KFZChangeGroupID=8 ) t WHERE t.rang = 1
Et si je n'ai pas bien compris, alors cf post d'iberserk ci-dessus !

Avec les variantes :

1) Utilisation d'une vue :

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW VUE_MAX (BASENR, KFZExchangeNoMax)
AS 
    SELECT BASENR, MAX(KFZExchangeNo)
    FROM   MIETVERTRAEGE
    GROUP BY BASENR ;
...
 
SELECT M.*
FROM   VUE_MAX AS T JOIN MIETVERTRAEGE AS M 
                                       ON  T.BASENR = M.BASENR
                                       AND T.KFZExchangeNoMax = M.KFZExchangeNo ;

2) Utilisation d'une CTE (Common table expression) :

Code:

1
2
3
4
5
6
7
8
9
10
11
WITH T (BASENR, KFZExchangeNoMax)
AS
(
    SELECT BASENR, MAX(KFZExchangeNo)
    FROM   MIETVERTRAEGE
    GROUP BY BASENR
)
SELECT M.* 
FROM   T JOIN MIETVERTRAEGE AS M 
                            ON  T.BASENR = M.BASENR
                            AND T.KFZExchangeNoMax = M.KFZExchangeNo ;

06/11/2012, 18h09
laloune

Citation:

Envoyé par Rei Ichido

Si j'ai bien compris il faut sélectionner le max de KFZExchangeNo pour chaque BaseNR.

c'est exactement ca.

et la requête proposée fonctionne tout à fait.

merci à tous pour votre aide et votre patience. j'observerai la procédure décrite par iberserk la prochaine fois.

Bonne soirée!