Requete SQL : SELECT MAX () & MAX() N-1

Version imprimable

08/10/2010, 08h38
olivier.x

Requete SQL : SELECT MAX () & MAX() N-1

Bonjour à tous,

Voilà je suis confronté à un problème.

J'ai créé une requête qui me permet de récupérer des informations en fonctions de la date la plus récente (SELECT MAX(DATE)). Ca c'est facile.

Mais maintenant j'ai besoin de récupéré les même info mais avec la dernière date précédent la dernière : un truc du genre : (SELECT MAX(date) N-1 )

Merci d'avance.
08/10/2010, 08h58
mikedavem

Bonjour,

Postez nous la DDL de votre table et un jeu d'essai ..
En principe pour faire cela je dirais que la fonction ROW_NUMBER() peut vous aider à réaliser ce que vous voulez faire.

++

Merci de votre réponse.

En faite je viens de trouver une solution mais la votre m'intéresse car elle est peut être plus optimisé.

Voici ma requête :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select 
ivd.item 		as [ACTICLE],
i.description		as [DESCRIPTION],
i.product_code 		as [CODE PRODUIT],
ivd.vend_num		as [N° FOURNISSEUR],
vadd.name		as [FOURNISSEUR],
ivd.rank		as [RANG],
vall.curr_code		as [DEVISE],
ivp.effect_date		as [DATE D APPLICATION],
ivp.brk_qty_conv##1	as [BORNE QUANTITE 1],
ivp.brk_cost_conv##1	as [PRIX UNITAIRE 1],
ivp.brk_qty_conv##2	as [BORNE QUANTITE 2],
ivp.brk_cost_conv##2	as [PRIX UNITAIRE 2],
ivp.brk_qty_conv##3	as [BORNE QUANTITE 3],
ivp.brk_cost_conv##3	as [PRIX UNITAIRE 3],
ivp.brk_qty_conv##4	as [BORNE QUANTITE 4],
ivp.brk_cost_conv##4	as [PRIX UNITAIRE 4],
ivp.brk_qty_conv##5	as [BORNE QUANTITE 5],
ivp.brk_cost_conv##5	as [PRIX UNITAIRE 5]
 
from 
dbo.vendor_all vall 
inner join dbo.vendaddr vadd on vall.vend_num = vadd.vend_num
inner join dbo.itemvend ivd on vadd.vend_num = ivd.vend_num
inner join dbo.itemvendprice ivp on ivd.item=ivp.item
inner join dbo.item i on ivp.item=i.item
inner join dbo.matltran mt on i.item=mt.item
 
where 
ivd.rank = '1'
and i.product_code in ('CAT','CAP')
and i.p_m_t_code = 'P'
and mt.trans_date > '2008-01-01'
and ivp.effect_date in (select top 2 effect_date from dbo.itemvendprice where item=ivd.item group by effect_date order by effect_date DESC)
 
 
GROUP BY
ivd.item
,i.description
,i.product_code
,ivd.vend_num
,ivd.rank
,vall.curr_code	
,vadd.name
,ivp.effect_date	
,ivp.brk_qty_conv##1
,ivp.brk_cost_conv##1
,ivp.brk_qty_conv##2
,ivp.brk_cost_conv##2
,ivp.brk_qty_conv##3
,ivp.brk_cost_conv##3
,ivp.brk_qty_conv##4
,ivp.brk_cost_conv##4
,ivp.brk_qty_conv##5
,ivp.brk_cost_conv##5
 
ORDER BY ivd.item ASC

08/10/2010, 14h48
Sergejack

Citation:

Envoyé par olivier.x

Merci de votre réponse.

En faite je viens de trouver une solution mais la votre m'intéresse car elle est peut être plus optimisé.

Plus optimisé : sans doute.
Plus sûre : absolument (car vous n'envisagez pas le cas où des dates seraient identiques).
08/10/2010, 14h57
olivier.x

Citation:

Plus sûre : absolument (car vous n'envisagez pas le cas où des dates seraient identiques).

Dans mon cas les dates ne peuvent être identiques car il s'agit d'une date et heure/min/seconde de saisie d'informations dans un ERP par conséquent il y a au moins quelques secondes d'écarts.
08/10/2010, 15h25
Sergejack

Citation:

Envoyé par olivier.x

Dans mon cas les dates ne peuvent être identiques car il s'agit d'une date et heure/min/seconde de saisie d'informations dans un ERP par conséquent il y a au moins quelques secondes d'écarts.

Il n'empêche que la solution avec ROW_NUMBER() n'a que des avantages (au pire dispensables).

Pas ROW_NUMBER... RANK ou DENSE_RANK dans le cas présent

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
WITH
T AS 
(
SELECT 
DENSE_RANK() OVER(PARTITION BY effect_date ORDER BY effect_date DESC) AS RANG,
ivd.item 		AS [ACTICLE],
i.description		AS [DESCRIPTION],
i.product_code 		AS [CODE PRODUIT],
ivd.vend_num		AS [N° FOURNISSEUR],
vadd.name		AS [FOURNISSEUR],
ivd.rank		AS [RANG],
vall.curr_code		AS [DEVISE],
ivp.effect_date		AS [DATE D APPLICATION],
ivp.brk_qty_conv##1	as [BORNE QUANTITE 1],
ivp.brk_cost_conv##1	as [PRIX UNITAIRE 1],
ivp.brk_qty_conv##2	as [BORNE QUANTITE 2],
ivp.brk_cost_conv##2	as [PRIX UNITAIRE 2],
ivp.brk_qty_conv##3	as [BORNE QUANTITE 3],
ivp.brk_cost_conv##3	as [PRIX UNITAIRE 3],
ivp.brk_qty_conv##4	as [BORNE QUANTITE 4],
ivp.brk_cost_conv##4	as [PRIX UNITAIRE 4],
ivp.brk_qty_conv##5	as [BORNE QUANTITE 5],
ivp.brk_cost_conv##5	as [PRIX UNITAIRE 5]
 
FROM 
dbo.vendor_all vall 
INNER JOIN dbo.vendaddr vadd ON vall.vend_num = vadd.vend_num
INNER JOIN dbo.itemvend ivd ON vadd.vend_num = ivd.vend_num
INNER JOIN dbo.itemvendprice ivp ON ivd.item=ivp.item
INNER JOIN dbo.item i ON ivp.item=i.item
INNER JOIN dbo.matltran mt ON i.item=mt.item
 
WHERE 
ivd.rank = '1'
AND i.product_code IN ('CAT','CAP')
AND i.p_m_t_code = 'P'
AND mt.trans_date > '2008-01-01'
 
GROUP BY
ivd.item
,i.description
,i.product_code
,ivd.vend_num
,ivd.rank
,vall.curr_code	
,vadd.name
,ivp.effect_date	
,ivp.brk_qty_conv##1
,ivp.brk_cost_conv##1
,ivp.brk_qty_conv##2
,ivp.brk_cost_conv##2
,ivp.brk_qty_conv##3
,ivp.brk_cost_conv##3
,ivp.brk_qty_conv##4
,ivp.brk_cost_conv##4
,ivp.brk_qty_conv##5
,ivp.brk_cost_conv##5
)
SELECT *
FROM   T
WHERE  RANG = 2
ORDER BY item ASC

A +

10/10/2010, 23h10
mikedavem

En principe ROW_NUMBER suffit dans le cas présent puisque qu'une date est en principe unique ... mais effectivement rien ne le garantit vraiment dans ce qui est dit ... (Unicité garantie ??)

Le mieux est en effet d'utiliser RANK ou DENSE_RANK pour éviter tout problème.

++
11/10/2010, 10h42
Sergejack

Il me semble que l'auteur cherche à récupérer 2 lignes (n et n -1) donc c'est bel et bien vers row_number qu'il devrait s'orienter et non vers (dense)rank.